数据库运用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"
)
')