1

Looking for insight as how to better control where SQL Server 2016 puts XML namespaces. Below is the query that formats the data correctly (please don't ask why the subData, I know not, wish it wasn't so, but it is... )

 WITH XMLNAMESPACES ('http://www.adomain.com' as ns2)
 select td1.id as '@id',
    (
    select td2.questionNumber, td2.questionType, td2.section
    from @TestData td2
    where td1.id = td2.id
    FOR XML PATH('subData'), TYPE
    ) as xml_output
   from @TestData td1
order by td1.questionNumber
 FOR XML path ('mainData'), ROOT('ns2:theRoot');

The output is spot on, but for some reason it always puts xmlns:ns2="http://www.adomain.com" on each subData. How does the query need to change so that the namespace is only on theRoot?

<ns2:theRoot xmlns:ns2="http://www.adomain.com">
    <mainData id="A_SHORTA">
        <xml_output>
            <subData xmlns:ns2="http://www.adomain.com">
                <questionNumber>A_SHORTA</questionNumber>
                <questionType>rad</questionType>
                <section>A</section>
            </subData>
        </xml_output>
    </mainData>
</ns2:theRoot>
Sam Carleton
  • 1,339
  • 7
  • 23
  • 45
  • This is a very annoying (but still correct) behaviour you cannot change *legally*. There are tons of related examples )and I'll pick one and close this as duplicate in a minute), just [one of my own answers](https://stackoverflow.com/q/49653985/5089204) with useful links, how to proceed with more or less ugly hacks :-D – Shnugo Jun 19 '18 at 15:37
  • You can try post-processing the XML with an XSLT identity transformation, this should compress the namespaces. – Tomalak Jun 19 '18 at 16:13

0 Answers0