How to create XML nodes with attributes from Table

Question

I have following example table

Code here

CREATE TABLE XMLData
(
    NodeName NVARCHAR(255),
    AttributA NVARCHAR(255),
    AttributB NVARCHAR(255),
    AttributC NVARCHAR(255),
) 

INSERT INTO XMLData VALUES 
('RowA','','abcd','efgh'),
('RowB','wxyz',NULL,NULL),
('RowC',NULL,'qwer','tyui'),
('RowD','stuv','erty','fghj')

SELECT * FROM dbo.XMLData

How can I get following XML ?

<NodeA>
  <NodeB />
  <NodeC AttributeX="">
    <RowA AttributeA="" AttributeB="abcd" AttributeC="efgh" />
    <RowB AttributeA="wxyz" />
    <RowC AttributeB="qwer" AttributeC="tyui" />
    <RowD AttributeA="stuv" AttributeB="erty" AttributeC="fghj" />
  </NodeC>
</NodeA>

I am beginner with XML, but I tried to play with something like this

SELECT
    (
    SELECT
        (
        SELECT '' AS '@AttributeX' FOR XML PATH('NodeC'),TYPE
        -- How to get table rows here ?
        )
    FOR XML PATH('NodeB'),TYPE -- Here it creates additional end NodeB tag
    )
FOR XML PATH('NodeA'),TYPE

Are you sure, that you really want exactly this output? It is not possible without dynamic SQL to set the Element's names dynamically. Normally I'd think of an Element "Row" with an attribut "RowID" which gets the values "RowA", "RowB" ... With this structure you could not analyse this with .nodes() ... — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 18, 2016 at 15:42
Btw: This is a really good question: Well articulated, clearly explained, sample data for copy and paste, expected output and own attempt. Cannot be better! +1 — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 18, 2016 at 15:50
HI Muflix! Is this question solved? Do you need further help? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 19, 2016 at 18:15
Hi Muflix, might be interesting for you: I placed a question myself how one could get the desired XML more easily: stackoverflow.com/q/35523127/5089204 — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 20, 2016 at 12:19

Community · Accepted Answer · 2017-05-23 12:13:41Z

2

This is - almost - what you wanted. As told in my comment you will not be able to create your Element's names (<RowA> etc.) dynamically. For this you'd have to use dynamic SQL and I assume this is not what you want actually...

Here's my suggestion:

SELECT '' AS [NodeB]
      ,'' AS [NodeC/@AttributeX]
      ,(
          SELECT x.NodeName AS [@NodeName]
                ,x.AttributA AS [@AttributeA]
                ,x.AttributB AS [@AttributeB]
                ,x.AttributC AS [@AttributeC]
          FROM XMLData AS x
          FOR XML PATH('Row'),TYPE
       ) AS NodeC
FOR XML PATH(''),ROOT('NodeA')

The result

<NodeA>
  <NodeB></NodeB>
  <NodeC AttributeX="">
    <Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" />
    <Row NodeName="RowB" AttributeA="wxyz" />
    <Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" />
    <Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" />
  </NodeC>
</NodeA>

This was an XSLT to get "dynamic" element names out of the XML above

Credits to Tim C

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output method="xml" indent="yes" />

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="*[@NodeName]">
        <xsl:element name="{@NodeName}">
            <xsl:apply-templates select="@*|node()"/>
        </xsl:element>
    </xsl:template>

    <xsl:template match="@NodeName" />
</xsl:stylesheet>

And this was the result Test it here

<NodeA>
  <NodeB/>
  <NodeC AttributeX="">
    <RowA AttributeA="" AttributeB="abcd" AttributeC="efgh"/>
    <RowB AttributeA="wxyz"/>
    <RowC AttributeB="qwer" AttributeC="tyui"/>
    <RowD AttributeA="stuv" AttributeB="erty" AttributeC="fghj"/>
  </NodeC>
</NodeA>

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Feb 18, 2016 at 15:49

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muflix Over a year ago

Thank you so much, unfortunately I really need to have element name same as the NodeName column but I will try to handle this with replace function. Dynamic SQL seems complicated to me, I will let you know.

Gottfried Lesigang Over a year ago

@Muflix, I hope that this qestion is solved now... If so, it would be very kind to accept my answer, thx!

Collectives™ on Stack Overflow

How to create XML nodes with attributes from Table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related