Insert node into XML without using sql:variable

Question

I have looked throughout the interwebs (including SO but I have probably missed it) for a way to insert a node into existing XML which is contained in a variable without first creating as XML in a variable a string of the node which I would like to insert and using "set @XMLVariable01.modify('insert sql:variable("@XMLVariable02") as ...".

From the example below I would like to get as the final result:

<P xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <L01>
    <A xsi:nil="true" />
    <B xsi:nil="true" />
    <C xsi:nil="true" />
  </L01>
  <L02>
    <A>2</A>
    <B xsi:nil="true" />
    <C>x</C>
  </L02>
</P>

Example:

declare
    @P xml;

declare
    @A varchar,
    @B varchar,
    @C varchar;

select
    @P = (
        select
            @A as [A],
            @B as [B],
            @C as [C]
        for xml path(N'L01'), root('P'), type, elements xsinil
        );

select @P; --Initial result

select
    @A = '2',
    @B = NULL,
    @C = 'x';

--select @P = ...?

select @P; --Final result

I did the following:

select
    @P = (
        select (
            select
                v.query('.')
            from @P.nodes('P/L01') as t (v)),
            (
            select
                @A as [A],
                @B as [B],
                @C as [C]
            for xml path(N'L02'), type, elements xsinil
            )
        for xml path(N'P'), type, elements xsinil
        );

Which comes close but I don't want the 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' anywhere other than in the initial/top line of the XML:

<P xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <L01>
    <A xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
    <B xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
    <C xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
  </L01>
  <L02 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <A>2</A>
    <B xsi:nil="true" />
    <C>x</C>
  </L02>
</P>

Edit (2018Dec14~1343): Based on Schnugo's helpful insight (Yes, I need/want the NULL values for the purpose of documentation that the indicated elements were accounted for but that no value was received. Also, I apologize but I am not able to fully grasp all that is being suggested in 1-5.), I have come up with the following:

select
    @P = (      
        select
            cast(replace(replace(cast(([XML].[Value]) as nvarchar(max)), cjR.Old01, cjR.New01), cjR.Old02, cjR.New02) as xml)
        from (
            select (
                select
                    v.query('.')
                from @P.nodes(N'P/L01') as t (v)),
                (
                select
                    @A as [A],
                    @B as [B],
                    @C as [C]
                for xml path(N'L02'), type, elements xsinil
                )
            for xml path(N'P'), type, elements xsinil
            ) as [XML] ([Value])
        cross join (
            select
                Value02 as Old01,
                N'' as New01,
                quotename(Value01, N'<>') as Old02,
                quotename(Value01 + Value02, N'<>') as New02
            from (
                select
                    N'P',
                    N' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"'
                ) as R (Value01, Value02)
            ) as cjR
        );

It seems like such a hacky way to produce what should be such a simple operation but apparently it is not. I welcome any further advice to improve on this.

score 0 · Accepted Answer · answered Dec 13 '18 at 10:27

You hit various flaws of SQL Server's XML support here...

With nested XML there's no other way then to use sub-queries. But the repeating namespaces in sub-queries are a very annoying issue. The XML produced is perfeclty okay, but the repeated namespace declarations can really bloat your XML enormously. This is discussed (and begged for a better solution) for centuries. There was a Connect-Ticket for more than 10 years with a lot of votes. But this disappeared together with Connect.

Do you really need NULLvalues to show off with xsi:nil? The default is to omit them...

Very often you'll see ugly workarounds
All of them use a CAST to NVARCHAR(MAX), manipulate the namespaces with string methods (STUFF, SUBSTRING, ...) and do a re-cast to XML at the end.

default namespace: Create the XML without the namespace and add the top node together with a namespace
prefixed namespace: Create the XML with a namespace placeholder and - again - use string methods (REPLACE) to correct this (look at this answer)
Your example seems to need xsi:nil to enforce NULLvalues not to be omited. This is even worse... You can create the XML as you do it and then use string methods, to throw away the wrong declarations.
You might build the whole thing on string level via concatenaion
There is FOR XML EXPLICIT. This is a weird and hard-to-learn format. But it is the only approach I know to set the namespaces right the way you want them.

I'm sorry, but this is exactly the kind of XML SQL-Server cannot build easily...

If you need help with one of the provided options, you might start a new question with a sepcific question on this.

Insert node into XML without using sql:variable

1 Answers1