本文主要是介绍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
这篇关于SQL Server2008 程序设计 XML的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
