数据库运用XML操作

1. xml index

alter table orders
add constraint pk_orders_orderid
primary key clustered(orderid)

create primary xml index xidx_details on orders(details)

create xml index xidx_details_path on orders(details) using xml index xidx_details for path

create xml index xidx_details_property on orders(details) using xml index xidx_details for property

create xml index xidx_details_value on orders(details) using xml index xidx_details for value


2. Demo XML表达式查询

declare @mydoc xml
set @mydoc = '
<AAA>
<BBB/>
<BBB/>
<CCC>
<DDD/>
<BBB>
<EEE/>
</BBB>
</CCC>
</AAA>
'
select @mydoc.query( ' //BBB ')
select @mydoc.query( ' //BBB[1] ')
select @mydoc.query( ' /AAA/BBB[1] ')
select @mydoc.query( ' /AAA/BBB[last()] ')

declare @mydoc xml
set @mydoc = '
<AAA>
<BBB ID="1"/>
<BBB ID="2"/>
<CCC>
<DDD/>
<BBB ID="3">
<EEE/>
</BBB>
</CCC>
</AAA>
'
select @mydoc.query( ' /AAA/BBB[@ID="1"] ')


select @myDoc.query( ' /bookstore/book/title ')

查询条件可以是attribute, 也可以是element，如下是element示例
select @myDoc.query( ' /bookstore/book[price>30] ')

3. FLOWER

declare @myDoc xml
set @myDoc = '
<AAA>
<BBB ID=" 1" />
<BBB ID="1" />
<BBB NAME="NAME1"/>
<CCC ID="1">
<DDD/>
<BBB>
<EEE ID="1"/>
</BBB>
<BBB/>
</CCC>
</AAA>
'
select @myDoc.query( '
for $id in /AAA/BBB
return $id ')

declare @myDoc xml
set @myDoc = '
<AAA>
<BBB>HELLO</BBB>
<BBB>Welcome</BBB>
<BBB NAME="NAME1"/>
<CCC ID="1">
<DDD/>
<BBB>OK
<EEE ID="1"/>
</BBB>
<BBB/>
</CCC>
</AAA>
'
select @myDoc.query( '
for $id in //BBB
return <result>{data($id)}</result> ')


declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING">
<title>Everyday</title>
<author>Giade De</author>
<price>30.00</price>
</book>
<book category="COMPUTER">
<title>Windows 2003</title>
<author>Mike</author>
<price>50.00</price>
</book>
<book category="SOFTWARE">
<title>VS.NET2003</title>
<author>Mike</author>
<price>90.00</price>
</book>
</bookstore>
'
select @myDoc.query( ' /bookstore/book/title ')
-- ---------------------------
select @myDoc.query( ' /bookstore/book[price>30] ')


select @myDoc.query( ' for $x in /bookstore/book
where $x/price>30
return $x/title ')

select @myDoc.query( ' for $x in /bookstore/book/title
order by $x
return $x ')

select @myDoc.query( ' for $x in /bookstore/book/title
return <li>{data($x)}</li> ')

select @myDoc.query( ' for $x in /bookstore/book/title
order by $x
return <li>{data($x)}</li> ')

value查询
declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING" id="1" >
<title>Everyday</title>
<author>Giade De</author>
<price>30.00</price>
</book>
<book category="COMPUTER" id="2" >
<title>Windows 2003</title>
<author>Mike</author>
<price>50.00</price>
</book>
<book category="SOFTWARE" id="3" >
<title>VS.NET2003</title>
<author>Mike</author>
<price>90.00</price>
</book>
</bookstore>
'
select @myDoc.value( ' (/bookstore/book/@id)[1] ', ' int ')

exist查询
select @myDoc.exist( ' /bookstore/book/title="VS.NET2003" ')


select @myDoc.exist( ' /bookstore/book[@id=1] ')

结果集中绑定表中列
select orderid, ' L01 ' as ProductID,Customer,
Details.query( '
<OrderDetails>
<Customer>{sql:column("Customer")}</Customer>
{
for $x in //row
return $x
}
</OrderDetails>
')
from orders

4.修改XML
INSERT
declare @doc xml
set @doc = ' <Products></Products> '

set @doc.modify(
' insert (<Product><ID>L01</ID><Name>LL01</Name></Product>)
into (/Products)[1] ')

set @doc.modify(
' insert (<Product><ID>L02</ID><Name>LL02</Name></Product>)
as first into (/Products)[1] ')

set @doc.modify(
' insert (<Product><ID>L03</ID><Name>LL03</Name></Product>)
as last into (/Products)[1] ')

set @doc.modify(
' insert attribute Price {"20.50"} into (/Products/Product)[1] ')

select @doc
-- ----------------------------------------
DELETE
declare @myDoc xml
set @myDoc = '
<bookstore>
<book category="COOKING" id="1" >
<title>Everyday</title>
<author>Giade De</author>
<price>30.00</price>
</book>
<book category="COMPUTER" id="2" >
<title>Windows 2003</title>
<author>Mike</author>
<price>50.00</price>
</book>
<book category="SOFTWARE" id="3" >
<title>VS.NET2003</title>
<author>Mike</author>
<price>90.00</price>
</book>
</bookstore>
'
set @mydoc.modify( ' delete (/bookstore/book[@id="1"]) ')

-- ------------------------------------

set @mydoc.modify( ' delete (/bookstore/book[@id="1"])[1] ')

-- --------------------------------------

set @mydoc.modify( ' delete (/bookstore/book/price)[1] ')
-- --------------------------

set @mydoc.modify( ' delete (/bookstore/book/price/text())[1] ')
-- ---------------------------
REPLACE
update text:

set @mydoc.modify( ' replace value of (/bookstore/book/price/text())[1] with "99.50" ')

update attribute:

set @mydoc.modify( ' replace value of (/bookstore/book/@id)[1] with "10" ')
-- ----------
set @mydoc.modify( '
replace value of (/bookstore/book/@id)[1]
with(
if(/bookstore/book[@id="1"]) then
"10"
else
"100"
)
')

本文转自高海东博客园博客，原文链接：http://www.cnblogs.com/ghd258/archive/2005/10/24/260707.html，如需转载请自行联系原作者