for xml path() concatenates texts, how to get 2 elements with the same name?
-
select'A' as [value],
'B' as [value]
for xml path('test'), type
This outputs the following:
<test><value>AB</value>
</test>
However, what I wanted is this:
<test><value>A</value>
<value>B</value>
</test>
I can do a lot with for xml path(), but such a basic thing is making me go crazy....
I know something like this:
select 'A' as [text()]union all select 'B' as [text()]
for xml path('value'), root('test'), type
will return the expected result, but that's not the point. In real life I need to construct a much more complex xml document, where potentially multiple elements with the same name can follow each other, and I need those values to be in multiple elements, not their concatenated values in one element?
So, how do I make for xml path() return the both values as 2 elements, even if they have the same name?
-
Bit weird but works...
select'A' as [value],
null as [x],
'B' as [value]
for xml path('test'), type
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Yeah, that was my workaround too. But I still think it is a bug though. I've reported it on connect: http://connect.microsoft.com/SQLServer/feedback/details/742314/for-xml-path-concatenates-elements-with-the-same-name. Please vote it up if you also think it should have returned 2 value elements.
This is most likely caused by the fact that for xml path() does need to concatenate elements if you specify a common parent node on 2 elements following each other. They probably forgot to limit this concatenation to all but the last level. For example:
select'A' as [parent/value1],
'B' as [parent/value2],
'C' as [value3]
for xml path('test'), type
Which returns (correctly):
<test><parent>
<value1>A</value1>
<value2>B</value2>
</parent>
<value3>C</value3>
</test>
-
The workaround suggested by Microsoft is to put a zero-length string in between the both elements:
selectnull [value],
'',
null [value]
for xml path('test'), type, elements xsinil
which produces:
<test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><value xsi:nil="true" />
<value xsi:nil="true" />
</test>
i.e. As opposed to the earlier trick to put an additional column with value null in between the both elements with the same name, the above demonstrated code also works if your query specifies "elements xsinil" on the for xml clause.
-
select
'A' as [test/value1],
'B' as [test/value2]
for xml path(''), type
-
And your point is? Because the output of your query is:
<test><value1>A</value1>
<value2>B</value2>
</test>
Which is not the same as the required output:
<test><value>A</value>
<value>B</value>
</test>
-
Agree. Ok then "Mark-101232" solution is already there.
Thanks.
-
iBar (5/23/2012)
Agree. Ok then "Mark-101232" solution is already there.Thanks.
Thank you for the effort, but I wasn't looking for an answer anymore. Microsoft already provided the 'official' workaround:
select'A' as [value],
'',
'B' as [value]
for xml path('test'), type
As demonstrated in my earlier post this is the only work around that also works if your for xml path() statement includes the modifier ", elements xsinil" to retrieve null values.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply