XPath in T-SQL update schema
In my table I have a column with XML
data.
For example lets say that schema is as below:
<person>
<id></id>
<name></开发者_开发问答name>
</person>
Problem is that some of these nodes has got additional node <lastname>
Now I would like to update all elements in this column so that each one has got node lastname
with default value, for example smith
.
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 does not exist inside <person>
, and insert <lastname>Smith</lastname>
into those XML values.
Update: if you want to pick certain 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
Update #2: to prove my query is right - try this little test here:
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
If you run this (on SQL Server 2008 or newer), you will get:
1 <person><id>1</id><name>John</name></person>
as the output; the selection criteria is testing on the XML element <name>
having a value of John
Check out this link:
http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx
There you can find this:
UPDATE docs SET xCol.modify('
insert
<section num="2">
<title>Background</title>
</section>
after (/doc//section[@num=1])[1]')
HTH
精彩评论