r.gall - Monday, February 11, 2019 6:23 AM

Eirikur Eiriksson - Monday, February 11, 2019 6:03 AM

r.gall - Monday, February 11, 2019 4:11 AM

My SQL outputs the following XML with attributes:

<Order_Notification Level="x">
<Order_Company Level="y2">
  <Order Level="z3" />
</Order_Company>
</Order_Notification>

However I need to output it without any attributes like so:

<Order_Notification>
<Order_Company>
  <Order>
</Order_Company>
</Order_Notification>

How can I modify the SQL below to output XML without any attributes?

SELECT    1 AS [Tag]
        ,NULL AS [Parent]
        ,'x' AS [Order_Notification!1!Level]
        ,NULL AS [Order_Company!2!Level]
        ,NULL AS [Order!3!Level]

UNION ALL

SELECT    2 AS [Tag]
        ,1 AS [Parent]
        ,'y1'
        ,'y2'
        ,'y3'

UNION ALL

SELECT    3 AS [Tag]
        ,2 AS [Parent]
        ,'z1'
        ,'z2'
        ,'z3'

FOR XML Explicit

Quick question, why are you using FOR XML EXPLICIT?
😎

I am using the explicit because it appears to give me more control over the XML I am generating. I have only posted a simple example here to get started, but  ultimately I will be generating up to 5 nested elements in XML from only 3 tables.