SQL Server2008 程序设计 XML

--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_xsd
AS '
<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 
where
idx.name='idx_l' and idx.type=1
order by column_id
/*
column_id   object_id   name                                                                                                                             system_type_id
----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- --------------
1           398624463   id                                                                                                                               165
2           398624463   nid                                                                                                                              56
3           398624463   tagname                                                                                                                          231
4           398624463   taguri                                                                                                                           231
5           398624463   tid                                                                                                                              56
6           398624463   value                                                                                                                            98
7           398624463   lvalue                                                                                                                           231
8           398624463   lvaluebin                                                                                                                        165
9           398624463   hid                                                                                                                              167
10          398624463   xsinil                                                                                                                           104
11          398624463   xsitype                                                                                                                          104
12          398624463   pk1                                                                                                                              56

(12 行受影响)

*/

 

 

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

create xml index idx_a on orderxml(xorders)
using xml index idx_l for path


create xml index idx_b on orderxml (xorders)
using xml index idx_l for value

create 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 int
declare @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,@xmlorder
select * from 
openxml(@handle,'/Root/Customer',1) with (CustomerID varchar(10),ContactName varchar(10))

/*
CustomerID ContactName
---------- -----------
BRU        Andrew Bru
ZAC        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