SQL Server 2005与XML的紧密整合

SQL Server 2005与XML的紧密整合

FOR 子句用来指定 BROWSE 或 XML 选项。BROWSE 和 XML 是无关的选项。 Transact-SQL 语法约定 语法 [ FOR { BROWSE | <XML> } ] <XML> ::= XML {        { RAW [ ( 'ElementName' ) ] | AUTO }        [            <CommonDirectives>            [ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]            [ , ELEMENTS [ XSINIL | ABSENT ]        ]      | EXPLICIT        [            <CommonDirectives>            [ , XMLDATA ]        ]      | PATH [ ( 'ElementName' ) ]        [            <CommonDirectives>            [ , ELEMENTS [ XSINIL | ABSENT ] ]        ] } <CommonDirectives> ::= [ , BINARY BASE64 ] [ , TYPE ] [ , ROOT [ ( 'RootName' ) ] ]   参数 BROWSE 指定可以在查看 DB-Library 浏览模式游标中的数据时进行更新。如果表包括 timestamp 列，并且表有 唯一索引，而且在发送到 SQL Server 实例的 SELECT 语句中 FOR BROWSE 选项位于末尾，则可以在应用 程序中浏览该表。 注意：   不能在包括 FOR BROWSE 选项的 SELECT 语句中使用 <lock_hint> HOLDLOCK。 FOR BROWSE 不能出现在由 UNION 运算符所联接的 SELECT 语句中。 注意：   如果表的唯一索引键列可为空，并且表在外部联接的内侧，则浏览模式不支持索引。 XML 指定以 XML 文档返回查询的结果。必须指定下列 XML 模式之一：RAW、AUTO、EXPLICIT。有关 XML 数据 和 SQL Server 的详细信息，请参阅使用 FOR XML 构造 XML。 RAW [ ('ElementName') ] 获得查询结果并将结果集内的每一行转换为以一般标识符 <row /> 作为元素标记的 XML 元素。（可选） 可以为该行元素指定名称。所得到的 XML 输出将使用指定的 ElementName 作为为每一行生成的行元素。 有关详细信息，请参阅使用 RAW 模式和 RAW 模式的增强功能。 AUTO 以简单的嵌套 XML 树返回查询结果。FROM 子句中每个在 SELECT 子句中至少列出一次的表都被表示为一 个 XML 元素。SELECT 子句中列出的列映射到适当的元素属性。有关详细信息，请参阅使用 AUTO 模式和 AUTO 模式的增强功能。 EXPLICIT 指定显式定义结果 XML 树的形状。使用该模式要求必须以一种特定的方式编写查询，即显式指定与想要 的嵌套有关的其他信息。有关详细信息，请参阅使用 EXPLICIT 模式和 EXPLICIT 模式的增强功能。 XMLDATA 返回内联 XDR 架构，但不将根元素添加到结果中。如果指定了 XMLDATA，则 XDR 架构将被追加到文档末 尾。 XMLSCHEMA [ ('TargetNameSpaceURI') ] 返回内联 XSD 架构。如果指定该指令（用于返回架构中指定的命名空间），则可以选择指定目标命名空 间 URI。有关详细信息，请参阅内联 XSD 架构生成。 ELEMENTS 指定列作为子元素返回。否则，列将映射到 XML 属性。只在 RAW、AUTO 和 PATH 模式中支持该选项。有 关详细信息，请参阅 RAW 模式的增强功能。 XSINIL 指定为 NULL 列值创建其 xsi:nil 属性设置为 True 的元素。该选项只能与 ELEMENTS 指令一起指定。 有关详细信息，请参阅使用 XSINIL 参数生成 NULL 值对应的元素。 ABSENT 指示对于空列值，将不在 XML 结果中添加对应的 XML 元素。该选项只能与 ELEMENTS 一起指定。 PATH [ ('ElementName') ] 为结果集中的每一行生成 <row> 元素包装。（可选）可以为 <row> 元素包装指定元素名称。如果提供了 空字符串（例如 FOR XML PATH ('')），则不生成包装元素。使用 PATH 可能为使用 EXPLICIT 指令所编 写的查询提供更简单的替代方案。有关详细信息，请参阅使用 PATH 模式。 BINARY BASE64 指定查询返回二进制 base64 编码格式的二进制数据。使用 RAW 和 EXPLICIT 模式检索二进制数据时， 必须指定该选项。这是 AUTO 模式中的默认值。 TYPE 指定查询以 xml 类型返回结果。有关详细信息，请参阅 FOR XML 查询中的 TYPE 指令。 ROOT [ ('RootName') ] 指定将一个顶级元素添加到结果 XML 中。（可选）可以指定要生成的根元素名称。如果不指定可选的根 名称，则添加默认的 <root> 元素。 示例 以下示例指定具有 TYPE 和 XMLSCHEMA 选项的 FOR XML AUTO。由于有 TYPE 选项，因此，结果集将作为 xml 类型返回到客户端。XMLSCHEMA 选项指定在所返回的 XML 数据中包括内联 XSD 架构，而 ELEMENTS 选项指定 XML 结果是以元素为中心的。 USE AdventureWorks; GO SELECT ContactID, FirstName, LastName, Phone FROM Person.Contact WHERE LastName LIKE 'G%' ORDER BY LastName, FirstName FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL; 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>        <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)