CODEWITHSUNDEEP
sql-serverxml
Carlo
Carlo

Reputation: 33

How to add multiple namespaces in sqlserver query to create XML

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!

Upvotes: 3

Views: 1690

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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>

Upvotes: 1

Related Questions