Nested FOR XML with namespaces

• weitzera

  Hall of Fame

  Points: 3442

  More actions

  August 16, 2011 at 4:38 pm

  #255671

  I have the following example query:

  DECLARE @Foo TABLE(value INT);

  DECLARE @bar TABLE(foo INT, value INT);

  DECLARE @baz TABLE(bar INT, value INT);

  INSERT INTO @Foo

  VALUES (1)

  INSERT INTO @bar

  VALUES (1,1)

  INSERT INTO @bar

  VALUES (1,2)

  INSERT INTO @baz

  VALUES (1,1)

  INSERT INTO @baz

  VALUES (1,2)

  INSERT INTO @baz

  VALUES (2,3)

  INSERT INTO @baz

  VALUES (2,4);

  WITH XMLNAMESPACES ('garbage' AS ns)

  SELECTfoo.value AS [ns:value],

  (SELECT bar.value AS [ns:value],

  (SELECT baz.value AS [ns:value]

  FROM @baz baz

  WHERE baz.bar = bar.value

  FOR XML PATH('ns:baz'), TYPE)

  FROM @bar bar

  WHERE bar.foo = foo.value

  FOR XML PATH('ns:bar'), TYPE)

  FROM @Foo foo

  FOR XML PATH('ns:foo'), ROOT('ns:garbage'), TYPE

  All is fine, except that each nested subquery redefines the namespace. I need the namespace to only be defined on the root element, the way it would with a straight PATH query. Is that possible, using PATH mode?

  To be clear, I need the values for a single parent to be combined within a single parent element in the resulting xml (hence, I can't use a single (non-nested) path query, since it does not combine rows.)

  I can't redefine the namespace on each node, since application code will later strip out the "garbage" element, and insert the XML fragment into a DOM that redefines the namespace.

  Thanks in advance!

  FYI, the xml result of that query is here:

  <ns:garbage xmlns:ns="garbage">

  <ns:foo>

  <ns:value>1</ns:value>

  <ns:bar xmlns:ns="garbage">

  <ns:value>1</ns:value>

  <ns:baz xmlns:ns="garbage">

  <ns:value>1</ns:value>

  </ns:baz>

  <ns:baz xmlns:ns="garbage">

  <ns:value>2</ns:value>

  </ns:baz>

  </ns:bar>

  <ns:bar xmlns:ns="garbage">

  <ns:value>2</ns:value>

  <ns:baz xmlns:ns="garbage">

  <ns:value>3</ns:value>

  </ns:baz>

  <ns:baz xmlns:ns="garbage">

  <ns:value>4</ns:value>

  </ns:baz>

  </ns:bar>

  </ns:foo>

  </ns:garbage>

  ---

  
  

  Dan Guzman - Not the MVP (7/22/2010)

  ---

  All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
  

• weitzera

  Hall of Fame

  Points: 3442

  More actions

  August 17, 2011 at 3:34 pm

  #1369885

  Ugh, never mind, it doesn't work...

  I've found a method that works; for each subquery, I add a garbage wrapper, and then strip it off, taking with it the namespace declaration. I'm going to call this technique "waxing":

  WITH XMLNAMESPACES ('garbage' AS ns)

  SELECTfoo.value AS [ns:value],

  (SELECT bar.value AS [ns:value],

  (SELECT baz.value AS [ns:value]

  FROM @baz baz

  WHERE baz.bar = bar.value

  FOR XML PATH('ns:baz'), ROOT('ns:garbage'), TYPE).query('ns:garbage/*')

  FROM @bar bar

  WHERE bar.foo = foo.value

  FOR XML PATH('ns:bar'), ROOT('ns:garbage'), TYPE).query('ns:garbage/*')

  FROM @Foo foo

  FOR XML PATH('ns:foo'), ROOT('ns:garbage'), TYPE

  I'd really love anyone's thoughts about this.

  Thanks,

  -a.

  ---

  
  

  Dan Guzman - Not the MVP (7/22/2010)

  ---

  All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'