update xml field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update xml field

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-12-03 : 04:18:22

Hi,
I have a table with xml datatype field. This table can be joined to a lookup table.
The lookup table has the values which need to be inside the original table.
I would like to update the values inside the xml datatype field.
Please correct my update query to do this.
If you run this, you will see what I require.
Thank you

declare @Sample table
(
UNID varchar(50),
[xml] XML
)

INSERT @Sample
SELECT 'x12345', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT 'x4321', NULL UNION ALL
SELECT 'x9876', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT 'x6789', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT * FROM @Sample

declare @tblLookup table
(
UNID varchar(50),
SecurityType varchar(50)
)

insert @tblLookup

select 'x9876', 'value99'
union all
select 'x6789', 'value77'

select * from @tblLookup

-----
--update
-- s
--set
-- s.xml = l.SecurityType
--from
-- @Sample s
-- inner join @Lookup l on s.UNID = l.UNID
------

--This is what the result should look like

declare @Result table
(
UNID varchar(50),
[xml] XML
)

INSERT @Result
SELECT 'x12345', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT 'x4321', NULL UNION ALL
SELECT 'x9876', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value99</value><value>value2</value></stringList>' UNION ALL
SELECT 'x6789', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value77</value><value>value2</value></stringList>'

SELECT * FROM @Result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:20:05

Another XML question?
What does Books Online say about inserting into XML data and updating XML data?

E 12°55'05.63"
N 56°04'39.26"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-12-03 : 04:24:30

It seems the only way to do this is to remove all the values and then re-insert with the correct value.
Is this the only way?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:39:37

What does Books Online say?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:42:50

I searched for "XML Data Modification" and found some examples, like

This example shows the use of the modify() method to insert a new <section> element to the right of the <section> element whose number is 1.

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

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:43:29

The following UPDATE statement replaces the <price> of a book whose ISBN is 1-8610-0311-0 to $49.99. The XML instance is typed with the XML schema http://myBooks, hence the namespace declaration in the XML data modification statement.

quote:
UPDATE XmlCatalog
SET Document.modify ('
declare namespace bk = "http://myBooks";
replace value of (/bk:bookstore/bk:book
[@ISBN="1-861003-11-0"]/bk:price)[1] with 49.99')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-03 : 04:44:13

The page i found is http://msdn.microsoft.com/en-us/library/ms345117.aspx

E 12°55'05.63"
N 56°04'39.26"

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-12-03 : 04:48:02

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources