SQL Server2008 程序设计 XML

来源：互联网发布：csgo皮肤评测知乎sg553 编辑：程序博客网时间：2024/06/07 06:40

--SQL Server2008 程序设计 XML           /********************************************************************************             *主题：SQL Server2008 程序设计 XML *说明：本文是个人学习的一些笔记和个人愚见             *      有很多地方你可能觉得有异议，欢迎一起讨论                         *作者：Stephenzhou(阿蒙)             *日期: 2012.12.6             *Mail:szstephenzhou@163.com             *另外：转载请著名出处。            **********************************************************************************/

--在开发中为了显示必须把关系数据库转换为xml，而由xsd类型的约束进行限制的成本是非常高的。
--对于希望执行验证

--SQL Server 架构集合
-- 定义个一个order_xsd
--下面架构定义了三个元素
--OrderAmt 使用了orderamt类型，它定义为int类型最少值为1 最大为4999

create xml schema collection dbo.order_xsdAS '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:simpleType name="orderAmt"><xsd:restriction base="xsd:int"><xsd:maxInclusive value="5000"/><xsd:minExclusive value="1"/></xsd:restriction></xsd:simpleType><xsd:element name="Order"><xsd:complexType><xsd:sequence><xsd:element name="CustomerName" type="xsd:string"/><xsd:element name="OrderDate" type="xsd:dateTime"/><xsd:element name="OrderAmt" type="orderAmt"/>   </xsd:sequence></xsd:complexType></xsd:element></xsd:schema>'go

--以架构创建表

create table xml_schematest(order_id int primary key ,order_xml xml(order_xsd)  --order_xml列绑定 order_xsd 架构)

---插入

insert into xml_schematest values(1,'<order/>')/*消息 6913，级别 16，状态 1，第 1 行--XML 验证: 找不到元素 'order' 的声明。位置:  :order[1]*/

正确的插入

insert into xml_schematest values(1,'<Order><CustomerName>Bill Gates</CustomerName><OrderDate>2012-12-16T14:22:27.25Z</OrderDate><OrderAmt>100</OrderAmt></Order>')insert into xml_schematest values(2,'<Order><CustomerName>Stephen Zhou</CustomerName><OrderDate>2012-12-06T14:22:27.25Z</OrderDate><OrderAmt>200</OrderAmt></Order>')select * from xml_schematest/*order_id    order_xml----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1           <Order><CustomerName>Bill Gates</CustomerName><OrderDate>2012-12-16T14:22:27.25Z</OrderDate><OrderAmt>100</OrderAmt></Order>2           <Order><CustomerName>Stephen Zhou</CustomerName><OrderDate>2012-12-06T14:22:27.25Z</OrderDate><OrderAmt>200</OrderAmt></Order>(2 行受影响)*/

--XML索引

建表插入数据

--XML索引create table OrderXml(orderdocid int primary key,xOrders xml not null)insert into OrderXml values(1,'<Orders><Order><Orderid>5</Orderid><CustomerName>Stephen</CustomerName><OrderAmount>12</OrderAmount></Order></Orders>')insert into OrderXml values(2,'<Orders><Order><Orderid>6</Orderid><CustomerName>Jack</CustomerName><OrderAmount>13</OrderAmount></Order></Orders>')insert into OrderXml values(3,'<Orders><Order><Orderid>7</Orderid><CustomerName>Jell</CustomerName><OrderAmount>14</OrderAmount></Order></Orders>')

--创建索引如下：

create primary xml index  idx_l on OrderXml(xOrders)select col.column_id,col.object_id,col.name,col.system_type_id from sys.columns col join sys.indexes idx on col.object_id=idx.object_id whereidx.name='idx_l' and idx.type=1order by column_id/*column_id   object_id   name                                                                                                                             system_type_id----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- --------------1           398624463   id                                                                                                                               1652           398624463   nid                                                                                                                              563           398624463   tagname                                                                                                                          2314           398624463   taguri                                                                                                                           2315           398624463   tid                                                                                                                              566           398624463   value                                                                                                                            987           398624463   lvalue                                                                                                                           2318           398624463   lvaluebin                                                                                                                        1659           398624463   hid                                                                                                                              16710          398624463   xsinil                                                                                                                           10411          398624463   xsitype                                                                                                                          10412          398624463   pk1                                                                                                                              56(12 行受影响)*/

--在主索引上创建XML索引

create xml index idx_a on orderxml(xorders)using xml index idx_l for pathcreate xml index idx_b on orderxml (xorders)using xml index idx_l for valuecreate xml index idx_c on orderxml (xorders)using xml index idx_l for property

for xml raw

大体生成数据无格式表示，其中返回的行列变为每个元素的属性。

for xml auto 输出是分层的。

select * from sales for xml raw/*<row 商品名="x1" 公司="X" 类别="A" 价格="100" /><row 商品名="x2" 公司="X" 类别="A" 价格="70" /><row 商品名="x3" 公司="X" 类别="B" 价格="100" /><row 商品名="y1" 公司="Y" 类别="A" 价格="60" /><row 商品名="y2" 公司="Y" 类别="B" 价格="80" /><row 商品名="y3" 公司="Y" 类别="C" 价格="100" /><row 商品名="z1" 公司="Z" 类别="B" 价格="60" /><row 商品名="z2" 公司="Z" 类别="B" 价格="80" /><row 商品名="z3" 公司="Z" 类别="C" 价格="100" />*/

新功能for xml path

用户把查询的结果自定义xml格式存入表或者传送

select 商品名 as [@商品名称],公司 as [公司类别/公司] ,类别 as [公司类别/类别],价格 from sales  for xml path /* <row 商品名称="x1">  <公司类别>    <公司>X</公司>    <类别>A</类别>  </公司类别>  <价格>100</价格></row><row 商品名称="x2">  <公司类别>    <公司>X</公司>    <类别>A</类别>  </公司类别>  <价格>70</价格></row><row 商品名称="x3">  <公司类别>    <公司>X</公司>    <类别>B</类别>  </公司类别>  <价格>100</价格></row><row 商品名称="y1">  <公司类别>    <公司>Y</公司>    <类别>A</类别>  </公司类别>  <价格>60</价格></row><row 商品名称="y2">  <公司类别>    <公司>Y</公司>    <类别>B</类别>  </公司类别>  <价格>80</价格></row><row 商品名称="y3">  <公司类别>    <公司>Y</公司>    <类别>C</类别>  </公司类别>  <价格>100</价格></row><row 商品名称="z1">  <公司类别>    <公司>Z</公司>    <类别>B</类别>  </公司类别>  <价格>60</价格></row><row 商品名称="z2">  <公司类别>    <公司>Z</公司>    <类别>B</类别>  </公司类别>  <价格>80</价格></row><row 商品名称="z3">  <公司类别>    <公司>Z</公司>    <类别>C</类别>  </公司类别>  <价格>100</价格></row> */

为输出的xml格式添加一个根元素如下

select * from Trade  for xml path, root('Root')  /*  <Root>  <row>    <Symbol>ADVW</Symbol>    <Delta>5</Delta>  </row>  <row>    <Symbol>BYA</Symbol>    <Delta>-5</Delta>  </row>  <row>    <Symbol>NWT</Symbol>    <Delta>3</Delta>  </row></Root>  */

生成内联架构

  select * from Trade  for xml auto, xmlschema

for xml auto ,elements生成基于元素的xml

使用OPENXML

declare @handle intdeclare @xmlorder varchar(1000)set @xmlorder='<Root><Customer  CustomerID="BRU" ContactName="Andrew Brust"><Order CustomerID="BRU" EmployeeID="5" OrderDate="2005-11-01"><OrderDetail OrderID ="10248" ProductID="16" Quantity="12"/><OrderDetail OrderID ="10248" ProductID="32" Quantity="10"/></Order></Customer><Customer  CustomerID="ZAC" ContactName="Bill Zack"><Order CustomerID="ZAC" EmployeeID="3" OrderDate="2005-11-16"><OrderDetail OrderID ="10283" ProductID="99" Quantity="3"/></Order></Customer></Root>'execute sp_xml_preparedocument @handle output,@xmlorderselect * from openxml(@handle,'/Root/Customer',1) with (CustomerID varchar(10),ContactName varchar(10))/*CustomerID ContactName---------- -----------BRU        Andrew BruZAC        Bill Zack(2 行受影响)*/

XML批量加载

CREATE TABLE TRADEXML (CUSID INT PRIMARY KEY IDENTITY,CUXML XML NOT NULL)INSERT INTO TRADEXML  SELECT * FROM    OPENROWSET(BULK 'D:/AA.XML',SINGLE_CLOB)AS DATAXML

明天继续。。。。

*作者：Stephenzhou(阿蒙)
*日期: 2012.12.6
*Mail:szstephenzhou@163.com
*另外：转载请著名出处。
*博客地址：http://blog.csdn.net/szstephenzhou