Monday, June 18, 2007 12:18 PM
royashbrook
SQL 2005 XML Primer
-- create a mini inventory structure
-- let's say it has toys in it
declare @x xml
set @x =
'<items>
<item id="1" type="toy">
<name>car</name>
<description>toy car</description>
<price>10</price>
</item>
<item id="2" type="toy">
<name>bike</name>
<description>toy bike</description>
<price>100</price>
</item>
<item id="3" type="sport">
<name>bike</name>
<description>real bike</description>
<price>100</price>
</item>
</items>'
--look at the toys
select
x.item.value('@id[1]','int') [id]
, x.item.value('@type[1]','varchar(20)') [type]
, x.item.value('name[1]', 'varchar(20)') [name]
, x.item.value('description[1]', 'varchar(20)') [description]
, x.item.value('price[1]', 'money') [price]
from
@x.nodes('//items/item') as x(item)
--wait, they should all be toys.
--what's that real bike doing in here?
--let's delete the non toys
set @x.modify('delete (/items/item[@type!="toy"])')
--hmm, that price is still way wrong on our toy bike, let's fix it
set @x.modify(
'replace value of
(/items/item[description/text() = "toy bike"]/price/text())[1]
with
"10"')
--yay!
select
x.item.value('name[1]', 'varchar(20)') [name]
, x.item.value('price[1]', 'money') [price]
from
@x.nodes('//items/item') as x(item)
Filed under: sql, xml, primer