August 16, 2011 at 4:38 pm
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.'
August 17, 2011 at 3:34 pm
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.'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply