How to add multiple namespaces in sqlserver query to create XML

Question

I try to create a XML with sqlserver that must contain multiple namespaces. The XML should look something like this:

<ns1:Message xmlns:ns1="http://Something/A"
                       xmlns:ns2="http://Something/B" 
                       xmlns:ns3="http://Something/C">
    <ns1:A>
        <ns1:A1>201608111003201</ns1:A1>
        <ns1:A2>Some text</ns1:A2>
        <ns1:A3>More text</ns1:A3>
    </ns1:A>
    <ns2:B>
        <ns2:B1>123456788</ns2:B1>
        <ns2:B2>Even more text</B2>
    </ns2:B>
    <ns3:C>
        <ns3:C1>E232323</ns3:C1>
        <ns3:C2>P</ns3:C2>
    </ns3:C>
</ns1:Message

My query does look something like this now with only one namespace.

WITH XMLNAMESPACES ('http://Something/A' as ns3,
                   'http://Something/B' as ns2,  
                   'http://Something/C' as ns1)

SELECT COLUMN1 as 'ns1:A1',
       COLUMN2 as 'ns1:A2',
       COLUMN3 as 'ns1:A3'
FROM MYTABLE
FOR XML PATH ('ns1:A'), ROOT('ns1:Message'), ELEMENTS

This query works fine, but when I try to add the ns2 or ns3 namespace in the query nothing seems to work. How must this be done. Thanks in advance!

Gottfried Lesigang · Accepted Answer · 2017-02-27 16:08:57Z

I do not quite understand, what you try to achieve...

This works:

WITH XMLNAMESPACES ('http://Something/A' as ns3,
                   'http://Something/B' as ns2,  
                   'http://Something/C' as ns1)

SELECT COLUMN1 as 'ns1:A1',
       COLUMN2 as 'ns2:A1',
       COLUMN3 as 'ns3:A1'
FROM MYTABLE
FOR XML PATH ('ns1:A'), ROOT('ns1:Message'), ELEMENTS

The element name "A1" will be there multiple times, but - due to the namespace - it is handled as different elements. That is the main purpose of a namespace.

In most cases there is a default namespace xmlns="SomeURL" and sub-namespaces like xmlns:sub1="SomeSubURL". Elements without a specific namespace belong to the default namespace, other elements would start with sub1:SomeName and therefore belong to the sub-namespace. But there's no need to define a default namespace.

I think you've got a misconception what a namespace is meant to be. Your example would not need a namespace... You are using nestings to group your data...

The following code would produce exactly the XML you want to reach, but this design seems over complicated... Maybe you have a good reason for this.

WITH XMLNAMESPACES ('http://Something/C' as ns1
                   ,'http://Something/A' as ns2
                   ,'http://Something/B' as ns3)

SELECT 201608111003201 AS [ns1:A/ns1:A1]
      ,'Some text' AS [ns1:A/ns1:A2]  
      ,'More text' AS [ns1:A/ns1:A3]

      ,123456788 AS [ns2:B/ns2:B1]
      ,'Even more text' AS [ns2:B/ns2:B2]

      ,'E232323' AS [ns3:C/ns3:C1]
      ,'P' AS [ns3:C/ns3:C2]
FOR XML PATH (''), ROOT('ns1:Message'), ELEMENTS

The result

<ns1:Message xmlns:ns3="http://Something/B" xmlns:ns2="http://Something/A" xmlns:ns1="http://Something/C">
  <ns1:A>
    <ns1:A1>201608111003201</ns1:A1>
    <ns1:A2>Some text</ns1:A2>
    <ns1:A3>More text</ns1:A3>
  </ns1:A>
  <ns2:B>
    <ns2:B1>123456788</ns2:B1>
    <ns2:B2>Even more text</ns2:B2>
  </ns2:B>
  <ns3:C>
    <ns3:C1>E232323</ns3:C1>
    <ns3:C2>P</ns3:C2>
  </ns3:C>
</ns1:Message>

Thanks this worked for me. I think my perception of namespaces was not quite right. The nesting of the groups worked for me. The XML is designed by some other company, no idea for the reason of this kind of design.

Collectives™ on Stack Overflow

How to add multiple namespaces in sqlserver query to create XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related