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.

Answer 2

Blatantly reusing imaginary DDL and sample data population by @PeterHe, here is a solution based on XQuery FLWOR expression.

SQL

-- DDL and sample data population, start
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.PARAMETER;

CREATE TABLE dbo.PARAMETER (
    PARAMETER_NAME NVARCHAR(64) PRIMARY KEY,
    PARAMETER_VALUE NVARCHAR(64) NOT NULL,
    PARAMETER_TYPE TINYINT NOT NULL,
);

INSERT dbo.PARAMETER (PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_TYPE)
VALUES ('PATH','D:\Work\test.xml',1),
    ('..','..',1),
    ('Test','TestValue',2);
-- DDL and sample data population, end

SELECT (SELECT * FROM dbo.PARAMETER
FOR XML PATH('row'), TYPE, ROOT('root')).query('<DynamicTable NAME="PARAMETER">
{
    for $x in /root/row
    return <Rows>
            <DynamicColumn NAME="PARAMETER_NAME" VALUE="{$x/PARAMETER_NAME/text()}" />
            <DynamicColumn NAME="PARAMETER_VALUE" VALUE="{$x/PARAMETER_VALUE/text()}" />
            <DynamicColumn NAME="PARAMETER_TYPE" VALUE="{$x/PARAMETER_TYPE/text()}" />
        </Rows>
}
</DynamicTable>') AS Result;

Output

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

Answer 3

You can use for xml path:

CREATE TABLE dbo.PARAMETER (
    PARAMETER_NAME nvarchar(64) NOT NULL,
    PARAMETER_VALUE nvarchar(64) NOT NULL,
    PARAMETER_TYPE tinyint NOT NULL,
    CONSTRAINT PK_PARAMETER PRIMARY KEY (PARAMETER_NAME)
)

INSERT dbo.PARAMETER (PARAMETER_NAME,PARAMETER_VALUE,PARAMETER_TYPE)
VALUES ('PATH','D:\Work\test.xml',1),
    ('..','..',1),
    ('Test','TestValue',2);

SELECT 'PARAMETER' AS '@NAME',
    (SELECT
        (SELECT 'PARAMETER_NAME' AS '@NAME',
                p1.PARAMETER_NAME AS '@VALUE'
        FROM dbo.PARAMETER p1
        WHERE p1.PARAMETER_NAME=p.PARAMETER_NAME
        FOR XML PATH ('DynamicColoumn'),TYPE),
        (SELECT 'PARAMETER_VALUE' AS '@NAME',
                p2.PARAMETER_VALUE AS '@VALUE'
        FROM dbo.PARAMETER p2
        WHERE p2.PARAMETER_NAME=p.PARAMETER_NAME
        FOR XML PATH ('DynamicColoumn'),TYPE),
        (SELECT 'PARAMETER_TYPE' AS '@NAME',
                p3.PARAMETER_TYPE AS '@VALUE'
        FROM dbo.PARAMETER p3
        WHERE p3.PARAMETER_NAME=p.PARAMETER_NAME
        FOR XML PATH ('DynamicColoumn'),TYPE)
    FROM dbo.PARAMETER p
    FOR XML PATH('Rows'),TYPE)
FOR XML PATH ('DynamicTable');

Output:

<DynamicTable NAME="PARAMETER">
  <Rows>
    <DynamicColoumn NAME="PARAMETER_NAME" VALUE=".." />
    <DynamicColoumn NAME="PARAMETER_VALUE" VALUE=".." />
    <DynamicColoumn NAME="PARAMETER_TYPE" VALUE="1" />
  </Rows>
  <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="Test" />
    <DynamicColoumn NAME="PARAMETER_VALUE" VALUE="TestValue" />
    <DynamicColoumn NAME="PARAMETER_TYPE" VALUE="2" />
  </Rows>
</DynamicTable>