Inserting an XML instance into another even though SQL type ‘xml’ is not supported in XQuery.


Since we shipped SQL Server 2005 I have seen a few users ask if there was a simple way to insert an XML instance into another one. For simplicity’s sake, in the rest of this post I’m going to assume that all my variables contain valid XML documents (a single top level element). The examples below can easily be adapted to deal with fragments (multiple roots)


 


Let’s imagine that we have two XML variables @x1 and @x2 and that we’d like to insert the instance from @x2 into the root element of the instance stored in @x1. We could try something like this


 


DECLARE @x1 XML, @x2 XML


SET @x1 = ‘<root/>’


SET @x2 = ‘<a/>’


SET @x1.modify(‘insert sql:variable(“@x2”) as first into /root[1]’)


go


Unfortunately, the above query returns the following error


XQuery: SQL type ‘xml’ is not supported in XQuery.


 


If the two instances were stored in the same variable, then we could easily proceed with the DML operation, as shown below


 


DECLARE @x XML


SET @x = ‘<root/>


<a/>’


SET @x.modify(‘insert /*[2] as first into /*[1]’)


SET @x.modify(‘delete /*[2]’)


SELECT @x


go


 


The results for the following script are


<root><a /></root>


 


So the question becomes: how do we combine two instances? There are two ways I know of.


 


The first method requires the conversion of both instances to a string type, like nvarchar(MAX) and the use of concatenation.


 


DECLARE @x1 XML, @x2 XML


SET @x1 = ‘<root/>’


SET @x2 = ‘<a/>’


DECLARE @x XML


SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))


SELECT @x


go


 


The second method was shown to me recently and involves FOR XML.


 


DECLARE @x1 XML, @x2 XML


SET @x1 = ‘<root/>’


SET @x2 = ‘<a/>’


DECLARE @x XML


SELECT @x = (SELECT @x1 “*”, @x2 “*” FOR XML PATH(), TYPE)


SELECT @x


go


 


 


With both scripts, variable @x ends up containing the value


<root /><a />


 


Now we can write a function that takes both the source and the target instance as parameters and returns the result. We’ll also make sure corner cases are handled gracefully.


 


CREATE FUNCTION f_insert_as_first (@x1 XML, @x2 XML)


RETURNS XML


AS


BEGIN


 


      — insert into null value is impossible


      IF (@x1 is null)


            RETURN null


 


    — if the value to insert is null or if @x1 contains no element we do nothing


      IF ((@x1.value(‘count(/*)’,‘int’) = 0) OR (@x2 is null))


            RETURN @x1


 


      — if there is no element to insert we do nothing


      IF (@x2.value(‘count(/*)’,‘int’) = 0)


            RETURN @x1


 


      — if any one of the two instances is not a valid document (more than one root element)


      — we do nothing


      IF ((@x1.value(‘count(/*)’,‘int’) > 1) OR (@x2.value(‘count(/*)’,‘int’) > 1))


            RETURN @x1 


 


 


      DECLARE @x XML


      SET @x = CONVERT(XML, (CONVERT(nvarchar(MAX), @x1) + CONVERT(nvarchar(MAX), @x2)))


      SET @x.modify(‘insert /*[2] as first into /*[1]’)


      SET @x.modify(‘delete /*[2]’)


 


      RETURN @x


 


END


go


 


Now we can use the function we just created to run our original scenario


 


DECLARE @x1 XML, @x2 XML


SET @x1 = ‘<root/>’


SET @x2 = ‘<a/>’


SET @x1 = dbo.f_insert_as_first(@x1, @x2)


SELECT @x1


 


The result will be as expected


<root><a /></root>


 


 


To end this post, let’s look at a scenario involving a table.


 


CREATE TABLE myTable (iCol int primary key,


xmlCol XML,


extraCol XML)


go


 


Now let’s imagine that for each row the instance contained in column extraCol should be inserted inside the instance contained in xmlCol.


 


Again, we could try something like this


UPDATE myTable SET xmlCol.modify(‘insert sql:column(“myTable.extraCol”) as first into /*[1]’)


but we already know that this is going to fail and return the following error


XQuery: SQL type ‘xml’ is not supported in XQuery.


 


The solution is to reuse our function and run


UPDATE myTable SET xmlCol = dbo.f_insert_as_first(xmlCol, extraCol)


 


You can populate the table with various values and check for yourself that the query performs the expected transformation.


 



Disclaimer:
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.


 

Comments (23)

  1. Martin says:

    Thanks. Very helpful

  2. k says:

    i have needed this loads of times so very helpful

  3. John says:

    But why do we do this? If the type is not supported and there is no real way to bypass that, this is certainly not a solution.

    What’s done here might work, but looking at the suggested code it’s using xml parsing and string concat, nothing I would wan’t do in code which require efficeny.

    I get the feeling that what we are trying to do here is not really intended to be done at all. DML is not very friendly towards updating with external data. Instead, you’d expect that the query to run should provide the xml that is to be inserted inline!?

  4. MSDNArchive says:

    John, I understand the concern regarding performance but this is a workaround, designed to bypass some internal limitations and it has its own drawbacks.

    Regarding the "friendliness" of DML with external data, sql:column() and sql:variable() were introduced for that very purpose. Unfortunately at this point they cannot handle XML data.

  5. jerry says:

    Microsoft should now get off their assess and provide xml support for inserting xml data

  6. MSDNArchive says:

    Thanks for the constructive comment Jerry. And frankly, how could we say no when you ask so nicely?

    I’m pretty sure I’m not betraying any oath of corporate secrecy when I tell you we are working with finite resources here and unfortunately, as much as we’d love to implement every feature we’re asked for, we have to prioritize. And the more feedback we get, the better we can estimate which features will have the most positive impact. So if you feel as strongly about this as your coment suggests I recommend that you file a feature request through http://connect.microsoft.com/sqlserver

  7. jerry says:

    denisruc

    It’s a no brainer – Microsoft introduces SQL 2005 with XML support, along with a new datatype – xml – and guess what?  DML does not support xml data types???  DUH ….

    It’s like me buying a new car and the car I bought doesn’t support tires.

    Come on, you get us all hyped up for true xml support (finally) but no support for the datatype in stored procedures…. very sloppy.

  8. Jeff says:

    Thanks for the workaround while we wait for MS to get a full implementation of XML in SQL. This works ok for adding things directly to the root node, but I’m having trouble adding XML nodes at specific places below the root.

    I’ve added a @location parm for the function to replace the ‘/*’, but unfortunately I then get the error "The argument 1 of the xml data type method "modify" must be a string literal."

    Making the statement dynamic doesn’t work, since the @x variable is then out of scope.

    Any suggestions?

  9. MSDNArchive says:

    Jeff,

    From what you wrote I believe you tried to parameterize your query by passing a string Variable to the modify method. Unfortunately that is not allowed and you must use a string literal.

    I also think I understand why you cannot use dynamic SQL but I would probably need a bit more detail to help you.

    I suggest you use the contact form on this blog to send me an email with a sample of what you’re trying to do.  I’ll look at it and try to help you.

    Also you can search the MSDN forums and the newsgroups to see if somebody else has already dealt with the same problem.

  10. Jerry says:

    "Also you can search the MSDN forums and the newsgroups to see if somebody else has already dealt with the same problem"

    YOU WON’T FIND IT.  Microsoft really, really, really, really needs to make this a priority.  Our company switched over to SQL 2005 soley for the XML feature.  We’re now discovering that there’s not a lot we can do inside procs regarding xml datatype – AND THIS IS REALLY STARTING TO HURT US.

    With all due respect, please get this enhancement submitted quick – we really need to be able to pass parameters of type XML and allow our procs to accept these parameters and insert them into existing xml strings.

  11. Brian says:

    Hi Jeff,

      Try this:

    — create scratch table ahead of time

    create table XMLScratchPad (xDoc xml);

    — define the xml documents

    declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX)

    set @xParentDoc = ‘<abc><def/></abc>’

    set @xNewNode = ‘<ghi/>’

    — set the dynamic sql

    set @sQuery = ‘UPDATE XMLScratchPad SET xDoc.modify(”insert ‘ + CAST(@xNodeNew AS NVARCHAR(MAX)) + ‘ AS LAST INTO (‘ + @sParentNode + ‘)[1]”)’

    — exec the sql

    exec(@sQuery)

    — get the result

    select @sParentDoc = xDoc from XMLScratchPad

  12. Jerry says:

    And what if @xNodeNew is over 8000 characters?

  13. Brian says:

    "Microsoft SQL Server 2005 introduces the max specifier. This specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data."

    — from BOL

    If you need more than that, you are SOL until Microsoft fixes this problem.

  14. Brian says:

    Actually 2^31-1 is 2 G which is also the limitation for xml types.

  15. Brian says:

    In my haste, it seems I left out a few lines, so let’s try this again:

    — create scratch table ahead of time

    create table XMLScratchPad (xDoc xml);

    — define the xml documents

    declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX), @sParentNode nvarchar(255)

    set @xParentDoc = ‘<abc><def/></abc>’

    set @xNewNode = ‘<ghi/>’

    set @sParentNode = ‘abc/def’

    insert into XMLScratchPad values(@xParentDoc)

    — set the dynamic sql

    set @sQuery = ‘UPDATE XMLScratchPad SET xDoc.modify(”insert ‘ + CAST(@xNewNode AS NVARCHAR(MAX)) + ‘ as last into (‘ + @sParentNode + ‘)[1]”)’

    — exec the sql

    exec(@sQuery)

    — get the result

    select @xParentDoc = xDoc from XMLScratchPad

    select @xParentDoc

  16. Brian says:

    In my haste, it seems I left out a few lines, so let’s try this again:

    — create scratch table ahead of time

    create table XMLScratchPad (xDoc xml);

    — define the xml documents

    declare @xParentDoc xml, @xNewNode xml, @sQuery nvarchar(MAX), @sParentNode nvarchar(255)

    set @xParentDoc = ‘<abc><def/></abc>’

    set @xNewNode = ‘<ghi/>’

    set @sParentNode = ‘abc/def’

    insert into XMLScratchPad values(@xParentDoc)

    — set the dynamic sql

    set @sQuery = ‘UPDATE XMLScratchPad SET xDoc.modify(”insert ‘ + CAST(@xNewNode AS NVARCHAR(MAX)) + ‘ as last into (‘ + @sParentNode + ‘)[1]”)’

    — exec the sql

    exec(@sQuery)

    — get the result

    select @xParentDoc = xDoc from XMLScratchPad

    select @xParentDoc

  17. kakali says:

    Hi!

    How to pass a dynamic argument in xquery?

    Say like this:

    declare @RootElement varchar(50)

    select @RootElement = ‘/Root/Metadata’

    Select

    m.DocumentId, m.MetadataId,

    ref.value( ‘./@DisplayField’, ‘varchar(8000)’ )

    from dbo.MetadataStore m

    cross apply StringValue.nodes(‘sql:variable("RootElement")’) as T(ref)

    –Gives Error :XQuery [dbo.MetadataStore.StringValue.nodes()]: A node or set of nodes is required for ‘nodes()’

    Any Idea?

  18. Brian says:

    kakali,

       The solution is a generic in the following format:

    – build a dynamic sql string (query or otherwise)

    – execute using "EXEC"

       The trick is to be able the access the results after the dynamic sql is done.  I would store the results in a work table (remember that temporary tables, e.g. #temp, are limited in scope to the EXEC function) to be picked up

  19. Jeff says:

    Thanks to all for your help. Brian thanks for pointing out about MAX now allowing for over 8000 chars. This frees me up to use Dynamic SQL on the large XML we have. Not the prettiest solution, but as long as it works…

  20. anil_76781 says:

    i want update my xml column data  

    i tried as fallows

    Update t set x.modify(‘replace value of (//Action/PXIIP/text())[1]  with "1.2.3.8"’)

    WHERE i between 1487 and 1497

    is working

    declare @ip varchar(100)

    set @ip=’1.2.3.7′

    select @ip

    Update t set x.modify(‘replace value of (//Action/PXIIP/text())[1]  with @ip’) WHERE i between 1487 and 1497

    is not working .

    but i want to pass as a parameter

  21. CMercs says:

    Has this been resolved (fixed/handled or otherwise enabled) in SQL Server 2008

  22. CMercs says:

    That would be a yes; http://www.sqlserverandxml.com/2008/01/insert-xml-variable-to-another.html

    But it doesn’t help us lowly 2005 users… :^(