Convert table to XML with attributes using T-SQL
I am trying to convert the tables:
- tblCustomAttributeSourceSchema that contains the COLUMN DEFINITIONS and
- tblLabelAttributes that contains the VALUES for each of the columns
For your convenience I created the tables in this sqlfiddle:
I would like to convert this to a table containing “LabelID” (of type INT – originally from tblLabelAttributes) and “XML_VALUE” (of type
XML) as per the following example. So for labelID=688 it should be:
<attributes> <attribute attribute_id="1" value="2.00" /> <attribute attribute_id="2" value="3.00" /> <attribute attribute_id="3" value="60.00"/> </attributes>
"attribute_id" should be set to the AttributeID from
tblCustomAttributeSourceSchema and the
"value" should be set to the value in
If an attribute value is null in ‘”tblLabelAttributes”‘ then the “attribute” record should be missing in the XML for that LabelID.
I am not very familiar with XML functionality in SQL Server. I am looking for how I could go about converting the data to such an XML. Any help would be greatly appreciated.
2 Solutions collect form web for “Convert table to XML with attributes using T-SQL”
Well you seem to have a few issues if the code is exactly like your fiddle:
- The data is pivoted, so you would do better to unpivot it.
- You don’t have a seeding number for an attribute so you need to create that.
- I still am not getting how you reference for use to the first table.
I do xml parsing and creation a lot for my job so here is an example of how I would do it:
; WITH d AS ( SELECT * , ROW_NUMBER() OVER(PARTITION BY LabelId ORDER BY val) AS rwn FROM tblLabelAttributes UNPIVOT (val FOR col IN (col1, col2, col3)) AS upvt ) , distincts AS ( SELECT DISTINCT LabelId FROM d ) Select LabelId AS "@LabelId" , ( SELECT val AS "@value" , rwn AS "@attribute_id" FROM d y WHERE y.LabelId = x.LabelId FOR XML PATH('attribute'), TYPE ) From distincts x FOR XML PATH('attributes'), ROOT('ROOT')
Generally speaking nested selects work well with xml creation as you at times need to show a child node relationship and IMHO they work well for this with doing an inner object to an outer object join in the where clause. You can also tell levels of what you are doing by at times having certain parts commented out for the ‘for xml …’. A good xml structure I usually build from the lowest nodes and then go up the tree. That way if I need to debug something I could comment out the last segment and see a section of xml exists on multiple lines. In this example the grouping would be the ‘LabelId’ if I comment out the last line.
The slightly adjusted version to fit the spec in the question:
; WITH d AS ( SELECT * , ROW_NUMBER() OVER(PARTITION BY LabelId ORDER BY val) AS rwn FROM tblLabelAttributes UNPIVOT (val FOR col IN (col1, col2, col3)) AS upvt ) , distincts AS ( SELECT DISTINCT LabelId FROM d ) Select LabelId AS "@LabelId" , ( SELECT val AS "@value" , rwn AS "@attribute_id" FROM d y WHERE y.LabelId = x.LabelId FOR XML PATH('attribute'), TYPE, ROOT('attributes') ) From distincts x
Use the FOR XML in your query:
SELECT * FROM tblCustomAttributeSourceSchema FOR XML AUTO SELECT * FROM tblLabelAttributes FOR XML AUTO
Alternatively, you can create your own XML in your t-sql code:
SELECT LabelID AS "@LabelID", col1 AS "Attributes/col1", col2 AS "Attributes/col2" FROM tblLabelAttributes FOR XML PATH('LabelID')
give an output like this:
<LabelID LabelID="688"> <Attributes> <col1>2.00</col1> <col2>3.00</col2> </Attributes> </LabelID>