1

I'm trying to get a result of FOR XML query as a text. I've found some similar question here: Using SQL Server "FOR XML": Convert Result Datatype to Text/varchar/string whatever? but in my case I use WITH XMLNAMESPACES statement and this solution doesnt work for me. How can I get a result of this following query as a text? 

WITH XMLNAMESPACES ('urn:blablablabla' as bi) 
SELECT 'urn:blablabla.xsd' AS "@xsi:schemaLocation",  

        (SELECT 'aaaa' AS 'bi:idValue', 
                'bbbb' AS 'bi:idContext' 
        FOR XML PATH('bi:Part1'),TYPE), 

        (SELECT 'cccc' AS 'bi:idValue', 
                'dddd' AS 'bi:idContext' 
        FOR XML PATH('bi:Part2'),TYPE) 
 FOR XML PATH('bi:Items'), ELEMENTS XSINIL

Thanks

The result should look like this: 

<bi:Items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bi="urn:blablablabla" xsi:schemaLocation="urn:blablabla.xsd">
<bi:Part1 xmlns:bi="urn:blablablabla">
 <bi:idValue>aaaa</bi:idValue>
 <bi:idContext>bbbb</bi:idContext>
</bi:Part1>
<bi:Part2 xmlns:bi="urn:blablablabla">
 <bi:idValue>cccc</bi:idValue>
 <bi:idContext>dddd</bi:idContext>
</bi:Part2>
</bi:Items

My goal is to get this result as a text and not as XML datatype. For example to do something like that: 

DECLARE @ResultText NVARCHAR(MAX) 
SET @ResultText = ... Returned text from this query ...

In addition maybe you know how can I avoid repeating of namespace in each node?

Thank you

Community
  • 1
  • 1
BGO
  • 45
  • 6

1 Answers1

1

You need to use select instead of set.

DECLARE @ResultText NVARCHAR(MAX);

WITH XMLNAMESPACES ('urn:blablablabla' as bi) 
SELECT @ResultText = 
  (
    SELECT 'urn:blablabla.xsd' AS "@xsi:schemaLocation",  

            (SELECT 'aaaa' AS 'bi:idValue', 
                    'bbbb' AS 'bi:idContext' 
            FOR XML PATH('bi:Part1'),TYPE), 

            (SELECT 'cccc' AS 'bi:idValue', 
                    'dddd' AS 'bi:idContext' 
            FOR XML PATH('bi:Part2'),TYPE) 
     FOR XML PATH('bi:Items'), ELEMENTS XSINIL 
);

Update:

avoid repeating of namespace

According to these two answers there is a way to do it with for xml explicit.

FOR XML PATH and xsi:nil attributes
How do I remove redundant namespace in nested query when using FOR XML PATH

If you want the behavior to change for the future you can vote on this connect item.
suppress namespace attributes in nested SELECT FOR XML statements

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • You are welcome. If this worked you should consider accepting this answer using the tick-mark to the right. It will show the community that this question has been answered. – Mikael Eriksson Apr 08 '12 at 18:27
  • Yes, It works, but maybe somebody knows how to avoid repeating of namespace in each node... – BGO Apr 08 '12 at 19:14
  • Sorry, forgot about that one. I don't know how to do it but I will update my answer if I figure it out. – Mikael Eriksson Apr 08 '12 at 19:23