SQL Server 2005与XML的紧密整合

怎样使用Typed XML

1、声明typed列或变量
CREATE TABLE HumanResources.EmployeeResume
(
 Emplyee INT,
 Resume XML (cvSchemas)
)

2、给typed XML赋值
INSERT INTO HumanResources.EmployeeResume
VALUES(1,''''<?xml version="1.0" ?>
 <resume xmlns="http://cvSchemas">
 ...</resume>''''

3、使用CONTENT或DOCUMET允许/禁止插入片段
CREATE TABLE Orders
(OrderID int IDENTITY(1,1),
 CustomerID int,
 OrderDetail xml (SalesSchema))

insert orders values(1,''''<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>'''')
--------------------------------
CREATE TABLE Orders
(OrderID int IDENTITY(1,1),
 CustomerID int,
 OrderDetail xml (DOCUMENT SalesSchema))

insert orders values(1,''''<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>
<Sales><Product><ID>1</ID><Name>p1</Name><Qty>100</Qty></Product></Sales>'''')

管理XML Indexes

1 建立主 XML index
alter table orders
add constraint pk_orders_orderid
primary key clustered(orderid)

CREATE PRIMARY XML INDEX xidx_item
ON Sales.Invoices(ItemList)

CREATE PRIMARY XML INDEX xidx_details
ON orders(details)

2 建立辅助 PATH XML index
CREATE XML INDEX xidx_ItemPath
ON Sales.Invoices(ItemList)
USING XML INDEX xidx_Item FOR PATH

CREATE XML PATH xidx_details_path
ON orders(details)
USING XML INDEX xidx_details FOR PATH

3 建立辅助 PROPERTY XML index
CREATE XML INDEX xidx_ItemProp
ON Sales.Invoices(ItemList)
USING XML INDEX xidx_Item FOR PROPERTY

CREATE XML INDEX xidx_details_property
ON orders(details)
USING XML INDEX xidx_details FOR PROPERTY

4 建立辅助 VALUE XML Index
CREATE XML INDEX xidx_ItemVal
ON Slaes.Invoices(ItemList)
USING XML INDEX xidx_Item FOR VALUE

CREATE XML INDEX xidx_details_value
ON orders(details)
USING XML INDEX xidx_details FOR VALUE

使用 XQuery 

1 什么是 XQuery
XQuery 是查询XML数据的语言
/InvoiceList/Invoice[@InvoiceNo=1000]

FLOWER 语句(for,let, order by, where,return)
语句      说明
for         循环通过同属节点
where   应用筛选标准
return   指定xml返回值

使用XQuery表达式 - 演示

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

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

使用XML数据类型的方法 

1 Use the query method
SELECT xmlCol.Query(
''''<InvoiceNumbers>
{
 for $i in .InvoiceList.Invoice
 return <InvoiceNo>
   {number($i/@InvoiceNum)}
     </InvoiceNuo>
}
</InvoiceNumbers>''''

select @myDoc.query(''''
for $id in //BBB
return <result>{data($id)}</result>'''')

2 Use the value method
SELECT xmlCol.value(
''''(/InvoiceList/Invoice/@InvoiceNo)[1]'''',''''int'''')

3 Use the exist method
SELECT xmlCol.exist(
''''/InvoiceList/Invoice[@InvoiceNo=1000]''''
)

4 Bind relational columns and variables
SELECT Invoices.query(
''''<Store>
 {sql:column("StoreName")}
</Store>''''

使用 Modify 方法修改 XML

1 Use the insert statement
SET @xmlDoc.modify(
''''insert element salesperson{"Bill"}
as first
into (/InvoiceList/Invoice)[1]'''')
------------------------------------------
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
-------------------------------------------

2 Use the replace statement
SET @xmlDoc.modify(
''''replace value of
(/InvoiceList/Invoice/SalesPerson/text())[1]
with "Ted"'''')
-------------------------------------------
set @mydoc.modify(''''replace value of (/bookstore/book/price/text())[1] with "99.50"'''')

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

3 Use the delete statement
SET @xmlDoc.modify(
''''delete
(/invoiceList/Invoice/SalesPerson)[1]'''')
-----------------------------------------
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>
 

上一页  [1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18