Geek asks and answers

XPath in T-SQL Update Schema

My table has a data column XML.

For example, let's say that the circuit is as follows:

<person>
  <id></id>
  <name></name>
</person>

The problem is that some of these nodes got extra nodes <lastname>

Now I would like to update all the elements in this column so that each of them gets a node lastnamewith a default value, for example smith.

+3
source share
2 answers

Try something like this:

UPDATE 
    dbo.YourTable
SET 
    XmlColumn.modify('insert <lastname>Smith</lastname> as last into (/person)[1]')
WHERE 
    XmlColumn.exist('/person/lastname') = 0

This updates all rows where the <lastname>node has no inside <person>and inserts <lastname>Smith</lastname>XML into these values.

Update: if you want to select specific names, use this query:

UPDATE 
    dbo.YourTable
SET 
    XmlColumn.modify('insert <lastname>Smith</lastname> as last into (/person)[1]')
WHERE 
    XmlColumn.exist('/person[name="John"]') = 1

# 2:, , - :

DECLARE @test TABLE (ID INT, XmlCol XML)

INSERT INTO @test 
   VALUES(1, '<person><id>1</id><name>John</name></person>'),
         (2, '<person name="John"><id>2</id><name>Fred</name></person>'),
         (3, '<person><id>3</id><name>Wally</name></person>')

SELECT *
FROM @test
WHERE XmlCol.exist('/person[name="John"]') = 1

( SQL Server 2008 ), :

1    <person><id>1</id><name>John</name></person>

; XML- <name>, John

+5

:

http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx

:

UPDATE docs SET xCol.modify('
  insert 
   <section num="2">   
        <title>Background</title>
   </section>
  after (/doc//section[@num=1])[1]')

0

More articles:


All Articles