SQL Server 2005与XML的紧密整合

王朝mssql·作者佚名 2006-01-10

宽屏版字体: 小|中|大|超大

FOR XML子句

增强说明

RAW模式下ELEMENTS RAW查询可以返回以元素为中心的XML结果

NULL值支持支持null值，可以在一元素为中心的结果中包含空值元素

Inline XSD schemas 可以生成inline XSD架构

TPYE指明返回xml数据类型值对于FOR XML查询，可以返回xml数据类型的值，使XML嵌套查询成为可能

PATH模式可以象XPath表达式一样定义XML结果

ROOT标识为结果集指定根元素

Elements命名为RAW和PATH模式查询指定命名元素

FOR XML子句范例

1、RAW模式下ELEMENTS

SELECT SaleID,ProductID,Quantity

FROM Sales

FOR XML RAW,ELEMENTS

2、NULL值支持

SELECT SaleID,ProductID,Quantity

FROM Sales

FOR XML RAW,ELEMENTS XSINIL

3、Inline XSD schemas

SELECT ProductID,Name,ListPrice

FROM Production.Product Product

FOR XML AUTO,XMLSCHEMA

4、TPYE指明返回xml数据类型值

SELECT ProductID,Name,ListPrice

(SELECT saleid,ProductID,Quantity

FROM Sales

WHERE Sales.ProductID-Products.ProductID

FOR XML RAW,TYPE)

FROM Products FOR XML AUTO

5、PATH模式

SELECT ProductID AS "@ProductID",

Name AS "Details/@Name",

Description AS "Details/text()"

FROM products FOR XML PATH

6、ROOT标识

SELECT ProductID,Name,ListPrice

FROM Products FOR XML AUTO,ROOT('PRODUCTS')

7、Elements命名

OPENXML函数

增强描述

文档可以是xml数据类型值 sp_xml_preparedocument存储过程支持xml参数

WITH子句支持XML数据类型在WITH子句中，XML类型数据能够被返回

Batch-level scoping 文档handle在批级有效，当查询批结束后，文档handle也被释放

OPENXML函数范例

declare @mydoc xml

set @mydoc='

<Name>Windows 2003</Name>

<Vendor>Vendor1</Vendor>

</Product>

<Vendor>Vendor2</Vendor>

</Product>

</Products>'

declare @docHandle int

Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc

SELECT * FROM OPENXML(@docHandle,'/Products/Product',2)

WITH (ID int,Name nvarchar(50),Vendor nvarchar(50))

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

declare @mydoc xml

set @mydoc='

</Products>'

declare @docHandle int

Exec sp_xml_preparedocument @docHandle OUTPUT,@mydoc

SELECT * FROM OPENXML(@docHandle,'/Products/Product',1)

WITH (Category nvarchar(50),ID int,Name nvarchar(50))

在数据库中存放XML

优点：

对结构化和非结构化数据实现单一存储

在关系模式中定义可变内容

选择最适合的数据类型

功能：

XML Indexes

基于XQuery的数据检索

基于XQuery的数据修改

XML架构支持：

Typed XML需要架构验证

UnTyped XML需要架构验证

怎样使用Untyped XML

声明xml数据类型

隐式转换字符串

显示转换字符值

使用Convert显示转换字符串

使用well-formed XML

Untyped XML范例

1、声明xml数据类型

CREATE TABLE Invoices

(

InvoiceID INT,

SalesDate DateTime,

CustomerID INT,

ItemList XML

)

DECLARE @itemDoc xml

2、隐式转换字符串值

SET @itemDoc = '<Items>etc.</Items>'

3、显示转换字符串

SET @itemDoc = CAST('<Items>etc.</Items>') AS XML

4、显示CONVERT显示转换字符串

SET @itemDoc = CONVERT(xml,'<Items>etc.</Items>')

5、使用well-formed XML

SET @itemDoc = CONVERT(XML,'<items>etc.')

ERROR!

怎样管理XML架构

1、建立XML架构集合

CREATE XML SCHEMA COLLECTION SalesSchema

'<?xml version="1.0" standalone="yes"?>

<xs:schema id="Sales" xmlns="http://www.gocean.com.cn" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

<xs:element name="Sales" msdata:IsDataSet="true" msdata:Locale="zh-CN">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="Product">

<xs:complexType>

<xs:sequence>

<xs:element name="ID" type="xs:int" minOccurs="0" />

<xs:element name="Name" type="xs:string" minOccurs="0" />

<xs:element name="Qty" type="xs:int" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

2、查看schema信息

SELECT * FROM sys.xml_schema_collections

SELECT * FROM sys.xml_namespaces

3、修改schema集合

ALTER XML SCHMEMA COLLECTION cvSchemas

4、删除schema集合

DROP XML SCHMEMA COLLECTION cvSchemas

怎样使用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>'

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/>

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

<CCC>

<DDD/>

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

<DDD/>

<BBB>OK

</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]$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 = '

<title>Everyday</title>

<author>Giade De</author>

</book>

<title>Windows 2003</title>

</book>

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

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

使用nodes方法转换XML输出

1 使用query, value和exist方法带xml变量

SELECT nCol.value('@ProductID','int') Product,

nCol.valus('@Quantity','int') Qty

FROM @xmlOrder.nodes('/Order/ListItem')

AS nTable(nCol)

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

declare @myDoc xml

set @myDoc = '

<title>Everyday</title>

<author>Giade De</author>

</book>

<title>Windows 2003</title>

</book>

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

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

declare @myDoc xml

set @myDoc = '

<title>Everyday</title>

<author>Giade De</author>

</book>

<title>Windows 2003</title>

</book>

</book>

</bookstore>

value查询

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

<Customer>{sql:column("Customer")}</Customer>

{

for $x in //row

return $x

}

</OrderDetails>

from orders

2 使用APPLY运算符

SELECT nCol.value('../@OrderID[1]','int') ID,

nCol.valus('@ProductID[1]','int') Prod

FROM Sales.Orders

CROSS APPLY OrderDoc.nodes('/Order/ListItem')

AS nTable(nCol)