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 !
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:
--A simple
AUTO
-mode query will return this--The result carries the table's name as element name and all columns as attributes
--You can proceed from here using XQuery FLWOR to re-structure the XML:
--The result
--Clean up
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 uselocal-name()
to get the attribute's name and$a
to retrieve its value.