Adding XML Node to all Rows in a Table

Question

I have a table with a column ID and a column XMLData. Unfortunately, the XML data column does not currently have any reference to the ID inside the XML itself, so if I export this column only and parse it, I would not be able to tie it back to the ID.

I am trying to added a node to the XML similar to below:

<AddedNode>
    <Id>10862832</Id>
</AddedNode>

Which would allow for the ID to be parsed from the XML itself, and be connected back. I tried adding the data via the below method:

With Test_CTE (Id, XMLData, AddedXML)
As
(
select Id, XMLData, cast('<AddedNode>
            <Id>' + cast(Id as varchar(20)) + '</Id>
        </AddedNode>' as varchar(max)) as AddedXML
from dbo.TestTable
)


update T
set XMLData.modify('insert ' + CTE.AddedXML + '
        as last into (/RootNode[1])
') 
from dbo.TestTable T
inner join Test_CTE CTE on CTE.Id = T.Id

But I receive the error "The argument 1 of the XML data type method "modify" must be a string literal."

I'm sure this has to do with me trying to pull the insert node via a column of data, but could anyone tell me how to fix it?

NOTE: I realize that I could also complete this via a while loop over entire table using variables, but there are ~2.5 million rows in the table and it would take longer than I want.

Gottfried Lesigang · Accepted Answer · 2017-03-30 16:12:24Z

1

If I get this correctly, this can be done much easier:

DECLARE @testtable TABLE(ID INT IDENTITY, XMLData XML);
INSERT INTO @testtable VALUES('<root><a>test</a></root>')
                            ,('<root><a>test</a><b>Some other</b></root>');

UPDATE @testtable SET XMLData.modify(N'insert <AddedNode><Id>{sql:column("ID")}</Id></AddedNode> as first into (/root)[1]');

SELECT * FROM @testtable;

The result

ID  XMLData
1   <root>
      <AddedNode>
        <Id>1</Id>
      </AddedNode>
      <a>test</a>
    </root>

2   <root>
      <AddedNode>
        <Id>2</Id>
      </AddedNode>
      <a>test</a>
      <b>Some other</b>
    </root>

UPDATE

Your own approach might be corrected to this (never build XML via string concatenation!):

WITH Test_CTE AS
(
    SELECT XMLData
         ,(SELECT ID AS [Id] FOR XML PATH('AddedNode'),TYPE)  as AddedXML
    FROM dbo.TestTable
)
UPDATE Test_CTE SET XMLData.modify(N'insert sql:column("AddedXML") as first into (/root)[1]');

SELECT * FROM dbo.TestTable;

edited Mar 30, 2017 at 16:12

answered Mar 30, 2017 at 16:04

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rdbradshaw Over a year ago

Well that was too easy. Thanks for the quick response. I will accept as soon as it opens up.

Gottfried Lesigang Over a year ago

@rdbradshaw Just to show you, where you went wrong, I placed an update how to correct your own attempt.

Collectives™ on Stack Overflow

Adding XML Node to all Rows in a Table

1 Answer 1

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related