DIV CSS 佈局教程網

RONG>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='
<Products>
  <Product Category="Book">
    <ID>1</ID>
    <Name>Windows 2003</Name>
    <Vendor>Vendor1</Vendor>
  </Product>
  <Product Category="Book">
    <ID>2</ID>
    <Name>VS.Net2003</Name>
    <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>
  <Product Category="Book" ID="1" Name="Windows 2003" />
  <Product Category="Book" ID="2" Name="VS.Net  2003" />
</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
AS
'<?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 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>
 &nbsp;  <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>
    <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]')
----------------------------------------------------------

使用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 = '
<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>')
----------------------------------------------------------
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>
'
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('
<OrderDetails>
<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)