Generate xml for Table data and column definitions

Question

I'm currently trying to generate an XML using a SQL statement, which returns the data from a table, as well as the table names, column names and associated data. So the structure of the table plus the data. The whole thing should be kept dynamic, so that a specification of the table name is enough to generate the xml.

As a result, I expect something like this:

<DynamicTable NAME="PARAMETER">
    <Rows>
        <DynamicColoumn NAME="PARAMETER_NAME" VALUE="PATH" />
        <DynamicColoumn NAME="PARAMETER_VALUE" VALUE="D:\Work\test.xml" />
        <DynamicColoumn NAME="PARAMETER_TYPE" VALUE="1" />
    </Rows>
    <Rows>
        <DynamicColoumn NAME="PARAMETER_NAME" VALUE=".." />
        <DynamicColoumn NAME="PARAMETER_VALUE" VALUE=".." />
        <DynamicColoumn NAME="PARAMETER_TYPE" VALUE="1" />
    </Rows>
</DynamicTable>

Important here is the table name and the column name with the corresponding data record.

Currently I'm trying to use FOR XML EXPLICIT to get there. But I fail to assign the datasets to individual rows. As well as the assignment between column name and dataset.

My current SQL:

SELECT  
    1               AS Tag,
    NULL            AS Parent,
    IS_T.TABLE_NAME AS [DynamicTable!1!NAME],
    NULL            AS [DynamicColoumn!2!NAME],
    NULL            AS [DynamicColoumn!2!VALUE]
FROM 
    INFORMATION_SCHEMA.TABLES AS IS_T
WHERE 
    IS_T.TABLE_NAME = 'PARAMETER'

UNION ALL

SELECT  
    2           AS Tag,
    1           AS Parent,
    'PARAMETER' AS [DynamicTable!1!NAME],
    P.PARA_NAME,
    P.PARA_VALUE    
FROM  
    PARAMETER AS P
FOR XML EXPLICIT

I would be very grateful for help and approaches !

Answer 1

I hope I did not get this wrong... From your question I take, that you need a generic approach to create this dynamic structure just from a table's name. So the example you provide is not the actual table you need this for. Correct?

If this is true you might have a look at FOR XML AUTO, which comes close to your needs completely out of the box:

First I create a tabel with rather random structure in order to simulate your issue and fill it with some random data:

CREATE TABLE dbo.AnyTable (
    SomeValue VARCHAR(50) NOT NULL,
    SomeOtherValue VARCHAR(50) NOT NULL,
    SomeNumber INT NOT NULL
);

INSERT dbo.AnyTable(SomeValue,SomeOtherValue,SomeNumber)
VALUES ('Value 1','Value 11',111)
      ,('Value 2','Value 22',222)
      ,('Value 3','Value 33',333);

--A simple AUTO-mode query will return this

SELECT * FROM dbo.AnyTable FOR XML AUTO

--The result carries the table's name as element name and all columns as attributes

<dbo.AnyTable SomeValue="Value 1" SomeOtherValue="Value 11" SomeNumber="111" />
<dbo.AnyTable SomeValue="Value 2" SomeOtherValue="Value 22" SomeNumber="222" />
<dbo.AnyTable SomeValue="Value 3" SomeOtherValue="Value 33" SomeNumber="333" />

--You can proceed from here using XQuery FLWOR to re-structure the XML:

SELECT
(
SELECT * FROM dbo.AnyTable FOR XML AUTO,TYPE
).query('<DynamicTable NAME="{local-name(/*[1])}">
         {
            for $r in /*
            return 
            <Rows>
            {
                for $a in $r/@*
                return <DynamicColumn NAME="{local-name($a)}" VALUE="{$a}" /> 
            }
            </Rows>
         }
         </DynamicTable>
        ');

--The result

<DynamicTable NAME="dbo.AnyTable">
  <Rows>
    <DynamicColumn NAME="SomeValue" VALUE="Value 1" />
    <DynamicColumn NAME="SomeOtherValue" VALUE="Value 11" />
    <DynamicColumn NAME="SomeNumber" VALUE="111" />
  </Rows>
  <Rows>
    <DynamicColumn NAME="SomeValue" VALUE="Value 2" />
    <DynamicColumn NAME="SomeOtherValue" VALUE="Value 22" />
    <DynamicColumn NAME="SomeNumber" VALUE="222" />
  </Rows>
  <Rows>
    <DynamicColumn NAME="SomeValue" VALUE="Value 3" />
    <DynamicColumn NAME="SomeOtherValue" VALUE="Value 33" />
    <DynamicColumn NAME="SomeNumber" VALUE="333" />
  </Rows>
</DynamicTable>

--Clean up

GO
DROP TABLE dbo.AnyTable;

The FLWOR-idea in short:

First we create the outer-most element and use the first element's local-name(), which is the table's name.
Then we run through the elements and open a <Rows> for each one.
Now we run through all attributes within the current element and add your <DynamicColumn>. We can use local-name() to get the attribute's name and $a to retrieve its value.