** 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='
1<products>
2<product category="Book">
3<id>1</id>
4<name>Windows 2003</name>
5<vendor>Vendor1</vendor>
6</product>
7<product category="Book">
8<id>2</id>
9<name>VS.NET2003</name>
10<vendor>Vendor2</vendor>
11</product>
12</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='
1<products>
2<product category="Book" id="1" name="Windows 2003"></product>
3<product category="Book" id="2" name="VS.NET 2003"></product>
4</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 = '
1<items>etc.</items>
'
3、显示转换字符串
SET @itemDoc = CAST('
1<items>etc.</items>
') AS XML
4、显示CONVERT显示转换字符串
SET @itemDoc = CONVERT(xml,'
1<items>etc.</items>
')
5、使用well-formed XML
SET @itemDoc = CONVERT(XML,'
1<items>etc.')
2ERROR!
3
4** 怎样管理XML架构 **
5
61、建立XML架构集合
7CREATE XML SCHEMA COLLECTION SalesSchema
8AS
9'<?xml version="1.0" standalone="yes"?>
10<xs:schema id="Sales" xmlns=" http://www.gocean.com.cn " xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:xs=" http://www.w3.org/2001/XMLSchema ">
11<xs:element msdata:isdataset="true" msdata:locale="zh-CN" name="Sales">
12<xs:complextype>
13<xs:choice maxoccurs="unbounded" minoccurs="0">
14<xs:element name="Product">
15<xs:complextype>
16<xs:sequence>
17<xs:element minoccurs="0" name="ID" type="xs:int"></xs:element>
18<xs:element minoccurs="0" name="Name" type="xs:string"></xs:element>
19<xs:element minoccurs="0" name="Qty" type="xs:int"></xs:element>
20</xs:sequence>
21</xs:complextype>
22</xs:element>
23</xs:choice>
24</xs:complextype>
25</xs:element>
26</xs:schema>
27'
28
292、查看schema信息
30SELECT * FROM sys.xml_schema_collections
31SELECT * FROM sys.xml_namespaces
32
333、修改schema集合
34ALTER XML SCHMEMA COLLECTION cvSchemas
35
364、删除schema集合
37DROP XML SCHMEMA COLLECTION cvSchemas
38
39** 怎样使用Typed XML **
40
411、声明typed列或变量
42CREATE TABLE HumanResources.EmployeeResume
43(
44Emplyee INT,
45Resume XML (cvSchemas)
46)
47
482、给typed XML赋值
49INSERT INTO HumanResources.EmployeeResume
50VALUES(1,'<?xml version="1.0" ?>
51<resume xmlns=" http://cvSchemas ">
52...</resume>'
53
543、使用CONTENT或DOCUMET允许/禁止插入片段
55CREATE TABLE Orders
56(OrderID int IDENTITY(1,1),
57CustomerID int,
58OrderDetail xml (SalesSchema))
59
60insert orders values(1,'<sales><product><id>1</id><name>p1</name><qty>100</qty></product></sales>')
61\--------------------------------
62CREATE TABLE Orders
63(OrderID int IDENTITY(1,1),
64CustomerID int,
65OrderDetail xml (DOCUMENT SalesSchema))
66
67insert orders values(1,'<sales><product><id>1</id><name>p1</name><qty>100</qty></product></sales>
68<sales><product><id>1</id><name>p1</name><qty>100</qty></product></sales>')
69
70** 管理XML Indexes **
71
721 建立主 XML index
73alter table orders
74add constraint pk_orders_orderid
75primary key clustered(orderid)
76
77CREATE PRIMARY XML INDEX xidx_item
78ON Sales.Invoices(ItemList)
79
80CREATE PRIMARY XML INDEX xidx_details
81ON orders(details)
82
832 建立辅助 PATH XML index
84CREATE XML INDEX xidx_ItemPath
85ON Sales.Invoices(ItemList)
86USING XML INDEX xidx_Item FOR PATH
87
88CREATE XML PATH xidx_details_path
89ON orders(details)
90USING XML INDEX xidx_details FOR PATH
91
923 建立辅助 PROPERTY XML index
93CREATE XML INDEX xidx_ItemProp
94ON Sales.Invoices(ItemList)
95USING XML INDEX xidx_Item FOR PROPERTY
96
97CREATE XML INDEX xidx_details_property
98ON orders(details)
99USING XML INDEX xidx_details FOR PROPERTY
100
1014 建立辅助 VALUE XML Index
102CREATE XML INDEX xidx_ItemVal
103ON Slaes.Invoices(ItemList)
104USING XML INDEX xidx_Item FOR VALUE
105
106CREATE XML INDEX xidx_details_value
107ON orders(details)
108USING XML INDEX xidx_details FOR VALUE
109
110** 使用 XQuery **
111
1121 什么是 XQuery
113XQuery 是查询XML数据的语言
114/InvoiceList/Invoice[@InvoiceNo=1000]
115
116FLOWER 语句(for,let, order by, where,return)
117语句 说明
118for 循环通过同属节点
119where 应用筛选标准
120return 指定xml返回值
121
122_ 使用XQuery表达式 - 演示 _
123
124declare @mydoc xml
125set @mydoc='
126<aaa>
127<bbb></bbb>
128<bbb></bbb>
129<ccc>
130<ddd></ddd>
131<bbb>
132<eee></eee>
133</bbb>
134</ccc>
135</aaa>'
136
137select @mydoc.query('//BBB')
138select @mydoc.query('//BBB[1]')
139select @mydoc.query('/AAA/BBB[1]')
140select @mydoc.query('/AAA/BBB[last()]')
141
142declare @mydoc xml
143set @mydoc='
144<aaa>
145<bbb id="1"></bbb>
146<bbb id="2"></bbb>
147<ccc>
148<ddd></ddd>
149<bbb id="3">
150<eee></eee>
151</bbb>
152</ccc>
153</aaa>'
154
155select @mydoc.query('/AAA/BBB[@ID="1"]')
156
157select @myDoc.query('/bookstore/book/title')
158
159查询条件可以是attribute, 也可以是element，如下是element示例
160select @myDoc.query('/bookstore/book[price>30]')
161
162declare @myDoc xml
163set @myDoc = '
164<aaa>
165<bbb>HELLO</bbb>
166<bbb>Welcome</bbb>
167<bbb name="NAME1"></bbb>
168<ccc id="1">
169<ddd></ddd>
170<bbb>OK
171<eee id="1"></eee>
172</bbb>
173<bbb></bbb>
174</ccc>
175</aaa>
176'
177select @myDoc.query('
178for $id in //BBB
179return <result>{data($id)}</result>')
180
181_ 使用XML数据类型的方法 _
182
1831 Use the query method
184SELECT xmlCol.Query(
185'<invoicenumbers>
186{
187for $i in .InvoiceList.Invoice
188return <invoiceno>
189{number( $i/@InvoiceNum )}
190
191}
192</invoiceno></invoicenumbers>'
193
194select @myDoc.query('
195for $id in //BBB
196return <result>{data($id)}</result>')
197
1982 Use the value method
199SELECT xmlCol.value(
200'( /InvoiceList/Invoice/@InvoiceNo)[1]','int' )
201
2023 Use the exist method
203SELECT xmlCol.exist(
204'/InvoiceList/Invoice[@InvoiceNo=1000]'
205)
206
2074 Bind relational columns and variables
208SELECT Invoices.query(
209'<store>
210{sql:column("StoreName")}
211</store>'
212
213_ 使用 Modify 方法修改 XML _
214
2151 Use the insert statement
216SET @xmlDoc.modify(
217'insert element salesperson{"Bill"}
218as first
219into (/InvoiceList/Invoice)[1]')
220\------------------------------------------
221INSERT
222declare @doc xml
223set @doc='<products></products>'
224
225set @doc.modify(
226'insert (<product><id>L01</id><name>LL01</name></product>)
227into (/Products)[1]')
228
229set @doc.modify(
230'insert (<product><id>L02</id><name>LL02</name></product>)
231as first into (/Products)[1]')
232
233set @doc.modify(
234'insert (<product><id>L03</id><name>LL03</name></product>)
235as last into (/Products)[1]')
236
237set @doc.modify(
238'insert attribute Price {"20.50"} into (/Products/Product)[1]')
239
240select @doc
241\-------------------------------------------
242
2432 Use the replace statement
244SET @xmlDoc.modify(
245'replace value of
246(/InvoiceList/Invoice/SalesPerson/text())[1]
247with "Ted"')
248\-------------------------------------------
249set @mydoc.modify('replace value of (/bookstore/book/price/text())[1] with "99.50"')
250
251set @mydoc.modify('replace value of ( /bookstore/book/@id)[1 ] with "10"')
252
253set @mydoc.modify('
254replace value of ( /bookstore/book/@id)[1 ]
255with(
256if(/bookstore/book[@id="1"]) then
257"10"
258else
259"100"
260)
261')
262\------------------------------------------
263
2643 Use the delete statement
265SET @xmlDoc.modify(
266'delete
267(/invoiceList/Invoice/SalesPerson)[1]')
268\-----------------------------------------
269declare @myDoc xml
270set @myDoc = '
271<bookstore>
272<book category="COOKING" id="1">
273<title>Everyday</title>
274<author>Giade De</author>
275<price>30.00</price>
276</book>
277<book category="COMPUTER" id="2">
278<title>Windows 2003</title>
279<author>Mike</author>
280<price>50.00</price>
281</book>
282<book category="SOFTWARE" id="3">
283<title>VS.NET2003</title>
284<author>Mike</author>
285<price>90.00</price>
286</book>
287</bookstore>
288'
289set @mydoc.modify('delete (/bookstore/book[@id="1"])')
290set @mydoc.modify('delete (/bookstore/book[@id="1"])[1]')
291set @mydoc.modify('delete (/bookstore/book/price)[1]')
292set @mydoc.modify('delete (/bookstore/book/price/text())[1]')
293\----------------------------------------------------------
294
295_ 使用nodes方法转换XML输出 _
296
2971 使用query, value和exist方法带xml变量
298SELECT nCol.value( '@ProductID','int' ) Product,
299nCol.valus( '@Quantity','int' ) Qty
300FROM @xmlOrder.nodes('/Order/ListItem')
301AS nTable(nCol)
302\----------------------------------------------------------
303declare @myDoc xml
304set @myDoc = '
305<bookstore>
306<book category="COOKING">
307<title>Everyday</title>
308<author>Giade De</author>
309<price>30.00</price>
310</book>
311<book category="COMPUTER">
312<title>Windows 2003</title>
313<author>Mike</author>
314<price>50.00</price>
315</book>
316<book category="SOFTWARE">
317<title>VS.NET2003</title>
318<author>Mike</author>
319<price>90.00</price>
320</book>
321</bookstore>
322'
323select @myDoc.query('/bookstore/book/title')
324\-----------------------------
325select @myDoc.query('/bookstore/book[price>30]')
326select @myDoc.query('for $x in /bookstore/book
327where $x/price>30
328return $x/title')
329
330select @myDoc.query('for $x in /bookstore/book/title
331order by $x
332return $x')
333
334select @myDoc.query('for $x in /bookstore/book/title
335return <li>{data($x)}</li>')
336
337select @myDoc.query('for $x in /bookstore/book/title
338order by $x
339return <li>{data($x)}</li>')
340\----------------------------------------------------------
341declare @myDoc xml
342set @myDoc = '
343<bookstore>
344<book category="COOKING" id="1">
345<title>Everyday</title>
346<author>Giade De</author>
347<price>30.00</price>
348</book>
349<book category="COMPUTER" id="2">
350<title>Windows 2003</title>
351<author>Mike</author>
352<price>50.00</price>
353</book>
354<book category="SOFTWARE" id="3">
355<title>VS.NET2003</title>
356<author>Mike</author>
357<price>90.00</price>
358</book>
359</bookstore>
360'
361_value查询_
362
363select @myDoc.value('( /bookstore/book/@id)[1]','int' )
364
365_ exist查询 _
366
367select @myDoc.exist('/bookstore/book/title="VS.NET2003"')
368select @myDoc.exist('/bookstore/book[@id=1]')
369\---------------------------------------------------------
370
371_ 结果集中绑定表中列 _
372
373select orderid,'L01' as ProductID,Customer,
374Details.query('
375<orderdetails>
376<customer>{sql:column("Customer")}</customer>
377{
378for $x in //row
379return $x
380}
381</orderdetails>
382')
383from orders
384
3852 使用APPLY运算符
386SELECT nCol.value( '../@OrderID[1]','int' ) ID,
387nCol.valus( '@ProductID[1]','int' ) Prod
388FROM Sales.Orders
389CROSS APPLY OrderDoc.nodes('/Order/ListItem')
390AS nTable(nCol)</items>