資料庫運用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/>

<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()]')

<BBB ID="1"/>

<BBB ID="2"/>

<BBB ID="3">

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 = '

<BBB ID=" 1" />

<BBB ID="1" />

<BBB NAME="NAME1"/>

<CCC ID="1">

<EEE ID="1"/>

select @myDoc.query('

for $id in /AAA/BBB

return $id')

<BBB>HELLO</BBB>

<BBB>Welcome</BBB>

<BBB>OK

for $id in //BBB

return <result>{data($id)}</result>')

<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 category="SOFTWARE">

<title>VS.NET2003</title>

<price>90.00</price>

</bookstore>

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

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')

return <li>{data($x)}</li>')

value查詢

<book category="COOKING" id="1" >

<book category="COMPUTER" id="2" >

<book category="SOFTWARE" id="3" >

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]')

'insert (<Product><ID>L02</ID><Name>LL02</Name></Product>)

as first into (/Products)[1]')

'insert (<Product><ID>L03</ID><Name>LL03</Name></Product>)

as last into (/Products)[1]')

'insert attribute Price {"20.50"} into (/Products/Product)[1]')

select @doc

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

DELETE

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，如需轉載請自行聯系原作者