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:

  • Optimizing SQL CONTAINS while using OR
  • ASP.NET MVC with SQL Server backend returns old data when query is executed
  • Truncate Table and UPDATE Statistics
  • how to display multiline text retrived from db
  • SQL Server Service Broker Performance Metrics
  • Convert a PL/SQL Stored Procedure in Oracle to Transact-SQL for SQL Server
  • http://www.sqlfiddle.com/#!6/b2fde/1

    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>
    

    The "attribute_id" should be set to the AttributeID from tblCustomAttributeSourceSchema and the "value" should be set to the value in tblLabelAttributes.

    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:

    1. The data is pivoted, so you would do better to unpivot it.
    2. You don’t have a seeding number for an attribute so you need to create that.
    3. 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>
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.