Dealing with namespaces and sql:variables in TSQL

 Uncategorized  Comments Off on Dealing with namespaces and sql:variables in TSQL
Feb 202015

There are two things we are going to discuss in this post:

  1. Dealing with namespaces in tsql without using the WITH XMLNAMESPACES statement
  2. Setting setting data from a sql variable in the modify statement

I have always struggled with the correct placement of the WITH XMLNAMESPACES statement: I can’t get it in the correct place or ‘escaped’ correctly, so I can never save the stored procedure. So we will show how you can use the methods to deal with XML without having to declare the WITH XMLNAMESPACES statement and in this case we are going to modify the existing value, passing in the variable from the sql statement.

To remove the use of WITH XMLNAMEPACES statement, you simply have to put in your xml method the following statement: ‘declare namespace ns0=””; {your work here}’

When using the modify statement, I wanted to pass a variable in from the stored procedure; you have to use the with sql:variable statement

Here is a simple example:

declare @xml XML = 
'<XML xmlns="">
 declare @replaceValue char(5)='ABCDE'
 set @xml.modify('declare namespace ns0="";
                  replace value of(ns0:XML/ns0:Record/ns0:Element/text())[1]
                  with sql:variable("@replaceValue")')
 select @xml

Which results in this xml

<XML xmlns="">

Notice that the original xml did not have a namespace prefix (ns0), however I added it to the queries.

Also notice in the sql the locations of the single and double quotes.