Trouble with Namespaces in T-SQL FOR XML queries

Question

I have a question about using SQL Server FOR XML to create XML documents with namespaces. I need to create a document like below where there is a namespace definition in the root element (xmlns:ijk="h:/i.j.k"), and a namespace reference in a child element attribute (ijk:LMNO="3333").

I am generating the XML to POST to a Web service:

<ROOT xmlns:ijk="h:/i.j.k" RSTU="1111">
  <CHILD CDEF="2222" ijk:LMNO="3333" />
</ROOT>

I wrote this first sample query to get the structure correct before I started fighting with namespaces, and the result is what is expected.

SELECT
    [RSTU] = 1111,
    (SELECT
         [CDEF] = 2222, [LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result

<ROOT RSTU="1111">
  <CHILD CDEF="2222" LMNO="3333" />
</ROOT>

Then I tried using WITH NAMESPACES to add the namespaces, but SQL Server gets carried away and adds all namespaces to all elements. The target Web service does not accept the XML with the namespace overload (in my real case, there are four levels of elements and three namespaces, and it makes a real mess).

WITH XMLNAMESPACES ('h:/i.j.k' as ijk, 'h:/x.y.z' as xyz)
SELECT
  [RSTU] = 1111,
  (SELECT
       [CDEF] = 2222, [ijk:LMNO] = 3333
  FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

<ROOT xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" RSTU="1111">
    <CHILD xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" CDEF="2222" ijk:LMNO="3333" />
</ROOT>

I read in Books Online that, while not recommended, namespaces can be added like a regular attribute. I tried this, and it generated the proper namespace definitions:

SELECT
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111, 
    (SELECT
         [CDEF] = 2222, [LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

<ROOT xmlns:ijk="h:/i.j.k" RSTU="1111">
  <CHILD CDEF="2222" LMNO="3333" />
</ROOT>

The output above has a good looking namespace definition, but the LMNO attribute does not have its required ijk: namespace reference prefix. I tried adding the namespace reference, but I got an error:

SELECT
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111,
    (SELECT
         [CDEF] = 2222, [ijk:LMNO] = 3333
     FOR XML RAW('CHILD'), TYPE)
FOR XML RAW('ROOT'), TYPE;

Result:

Msg 6846, Level 16, State 2, Line 34
XML name space prefix 'ijk' declaration is missing for FOR XML column name 'ijk:LMNO'.

Is it possible to write a T-SQL FOR XML query that generates XML where:

1. a namespace is defined only in the root element, and

2. the root element has data attributes other than namespace definitions, and

3. references to the namespace are used in attribute names in child elements?

I reviewed How do I remove redundant namespace in nested query when using FOR XML PATH. In this topic the root element has only namespace definitions and no data attributes.

Answer 1

This is ugly, but a workaround

SELECT
    CAST(REPLACE(CAST(
    (SELECT
    [xmlns:xyz] = 'h:/x.y.z',
    [xmlns:ijk] = 'h:/i.j.k',
    [RSTU] = 1111,
    (SELECT
         [@CDEF] = 2222, [@ns_ijk_LMNO] = 3333
     FOR XML PATH('ROOT'), TYPE)
     FOR XML RAW('CHILD'), TYPE) AS NVARCHAR(MAX)),'ns_ijk_','ijk:') AS XML);

The result

<CHILD xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" RSTU="1111">
  <ROOT CDEF="2222" ijk:LMNO="3333" />
</CHILD>

By using the RAW mode for the outer SELECT it is allowed to place namespace declarations just as if they were attributes.

The internal FOR XML PATH will not use these namespaces (other behavior with WITH XMLNAMESPACES!), but it is not possible to use a namespace prefix there.

So I add something to the attributes name, cast the whole XML to NVARCHAR(MAX), replace my dummy and cast it back.

Please go to the connect issue and vote. This is really annoying!

The repeated namespaces (when using sub-selects) are not wrong but bloating the output and can clash in validators.

Answer 2

With your result

<ROOT xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" RSTU="1111">
    <CHILD xmlns:xyz="h:/x.y.z" xmlns:ijk="h:/i.j.k" CDEF="2222" ijk:LMNO="3333" />
</ROOT>

It seems like you have an extraneous xyz namespace, but the rest seems valid. Defining a namespace isn't the same as applying a namespace to an element or attribute, and defined namespaces can be ignored if they aren't applied (i.e. prepending as on your LMNO element). Defining the namespace twice is redundant but shouldn't be invalid.

XML is a finicky standard so perhaps the probably is really on the validation which 'requires' this.

Doesn't change your problem, of course, but in the same way that validators have strange expectations, many XML generators don't give this type of flexibility over optional values. They often have the 'this is just what you get' approach.