CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
Schultz9999
Schultz9999

Reputation: 8926

T-SQL DML: how to change/add a parent of an existing XML node?

I have several XML in my DB that need to be updated. Here is the simplistic representation of them:

<root>
  <child1>blah</child1>
</root>

I need to wrap <child1> with another element to get structure like this:

<root>
  <child1Root>
    <child1>blah</child1>
  </child1Root>
</root>

Appears easy but I am not T-SQL and DML specialist.

Note: if one is interested in knowing why to update, the answer is that the XML below is not deserializable using DataContractSerializer. It can be deserialized using XmlSerializer and XmlArray attribute but not DCS:

<root>
  <child1>blah</child1>
  <child2>blah</child2>
</root>

Upvotes: 0

Views: 1456

Answers (3)

wBob
wBob

Reputation: 14379

If your actual code is as simple as your example then you could just do this:

DECLARE @yourTable TABLE ( yourXML XML )

INSERT INTO @yourTable ( yourXML )
VALUES 
    ( '<root><child1>blah1</child1></root>' ),
    ( '<root><child1>blah2</child1></root>' )

UPDATE @yourTable
SET yourXML = yourXML.query('root/child1').query('<root><child1Root>{.}</child1Root></root>') 

SELECT * FROM @yourTable

If your real XML is a bit more complicated then you might need to break it up.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Here's a working sample. I can think of no reason why the string manipulations would not work, given that valid XML has strict rules. At the very least, "<" which is key to the string searches should never exist outside of XML tag names.

-- create a sample table variable with a few variations
declare @T table (sample xml)
insert @T select '
<root>
  <child1>blah</child1>
</root>'
insert @T select '
<root>
  <child1>blah1</child1>
  <child1>blah2</child1>
</root>'
insert @T select '
<root>
  <child1>
    blah1
    <child2>blah2</child2>
  </child1>
</root>'
insert @T select '
<root>
<child0>
  <child1>
    <child4>
      <child3>blah2</child3>
    </child4>
  </child1>
</child0>
</root>'

-- peek at the content
select * from @T

-- perform the replacements as many times as required
-- note that the string "stackoverflow123" is expected to NEVER
--    exist in your data, or use another string if it does!
while @@rowcount > 0
begin
    update T
    set sample = stuff(X.A, Y.B, Z.C - Y.B + 9, '<child1Root>'+
        replace(replace(
            SUBSTRING(X.A, Y.B, Z.C - Y.B + 9),
            '<child1>','<stackoverflow123>'),
            '</child1>','</stackoverflow123>')
                +'</child1Root>')
    from @T T
    cross apply (
        select convert(varchar(max),sample)) X(A)
    cross apply (
        select patindex('%<child1>%</child1>%', X.A)) Y(B)
    cross apply (
        select charindex('</child1>', X.A, Y.B+1)) Z(C)
    where Z.C > 0
end

-- finally revert the placeholder string back to "child1"
update @T
set sample = replace(convert(varchar(max),sample), 'stackoverflow123', 'child1')

-- inspect the finished product
select * from @T

Upvotes: 0

Schultz9999
Schultz9999

Reputation: 8926

Brute force approach:

DECLARE @myDoc xml       
SET @myDoc = '
<root>       
    <child1>blah</child1>       
</root>'       
SELECT @myDoc       

DECLARE @child1 xml;
SET @child1 = @myDoc.query('/root/child1')
SELECT @child1

SET @myDoc.modify('       
insert <child1Root />
into (/root)[1]') 
SELECT @myDoc       

SET @myDoc.modify('       
insert sql:variable("@child1")
into (/root/child1Root)[1]') 
SELECT @myDoc       

SET @myDoc.modify('       
delete /root/child1') 
SELECT @myDoc

Upvotes: 0

Related Questions