第四章、XML集成(定义XML数据列、XML模式集合、XML数据类型方法 .query,.value,.modify,.nodes,.exists),关系数据转成XML

注意：XML区分大小写

一、XML数据类型

XML数据类型是sql2005率先引入的。

1、定义XML数据的列

CREATE TABLE [Production].[ProductModel](
	[ProductModelID] [int] IDENTITY(1,1) NOT NULL,[Name] [dbo].[Name] NOT NULL,--[CatalogDescription]表中的格式
	[CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,[Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,[ModifiedDate] [datetime] NOT NULL,CONSTRAINT [PK_ProductModel_ProductModelID] PRIMARY KEY CLUSTERED 
(
	[ProductModelID] ASC
)WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2、XML模式集合

XML架构本身也是一个XML，只不过它是用于描述XML字段中内容的结构。
推荐阅读：sql Server 2005学习笔记之 XML架构
http://blog.csdn.net/u014038143/article/details/78192045（推荐）
http://blog.csdn.net/u014038143/article/details/78192044（注意：" 是键盘逗号键上面）

实例：

--创建XML架构集合
CREATE XML SCHEMA COLLECTION MyXMLSchema
AS
'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="books">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="book" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
'

--查询库中的XML架构集合
Select * from sys.xml_schema_collections


--创建表
CREATE TABLE [cs](
	[ID] [int] IDENTITY(1,[Books] [xml](CONTENT [MyXMLSchema]) NULL,)



--定义一个绑定到架构MyXMLSchema架构集合的XML变量
Declare @MyXML AS XML(MyXMLSchema)
--赋予结构正确的XML数据
Set @MyXML =
'
<books>
<book>电话号码大全</book>
</books>
'
--插入数据
insert into cs values(@MyXML)

3、修改和删除XML模式集合

（1）、修改XML模式集合
（2）、删除XML模式集合

--修改XML架构集合
ALTER XML SCHEMA COLLECTION MyXMLSchema
ADD
'
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="Journals">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Journal" type="xsd:string" maxOccurs="100"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
'


--删除XML架构集合
drop XML SCHEMA COLLECTION MyXMLSchema

4、XML数据类型方法

(1).query

类似于SQL查询，只是结果匹配于XML数据节点

实例1：

declare @myDoc xml  
set @myDoc = '<Root>  
<ProductDescription ProductID="1" ProductName="Road Bike">  
<Features>  
  <Warranty>1 year parts and labor</Warranty>  
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>  
</Features>  
</ProductDescription>  
</Root>'  
SELECT @myDoc.query('/Root/ProductDescription/Features')

查询返回结果

<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>

实例2：

--查找Production.ProductModel表的Instructions字段中step
--declare namespace 声明的命令空间必须在一行
select ProductModelID,Instructions.query('declare namespace PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/PI:root/PI:Location/PI:step') 
from Production.ProductModel
where ProductModelID=66

或者是

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as PI) 

select ProductModelID,Instructions.query('/PI:root/PI:Location/PI:step') 
from Production.ProductModel
where ProductModelID=66

原Instructions的XML内容

<?xml version="1.0" encoding="utf-8"?>

<root xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
  Adventure Works CyclesWA-620 Instructions Assembling the LL Touring Seat Summary: This document contains manufacturing instructions for assembling the LL Touring Seat,Product Model 63. Instructions are work center specific and are identified by work center ID. These instructions must be followed in the order presented. Deviation from the instructions is not permitted unless an authorized Change Order detailing the deviation is provided by the Engineering Manager.
  <Location LaborHours="1.5" LotSize="1" LocationID="50">
    Work Center 50 - SubAssembly. The following instructions pertain to Work Center 50. (Setup hours = .0,Labor Hours = 1.5,Machine Hours = 0,Lot Sizing = 1)
    <step>
      Put the
      <material>Seat post Lug (Product Number SL-0931)</material> on the
      <material>Seat Post (Product Number SP-2981)</material>.
    </step>
    <step>
      Insert the
      <material>Pinch Bolt (Product Number PB-6109)</material> and tighten until it is secure but still able to slide up or down the post as shown in illustration
      <diag>6</diag>.
    </step>
    <step>
      Attach the
      <material>LL Seat (Product Number SE-T312)</material> to the top of the Seat Post and tighten securely.
    </step>
    <step>
      Inspect per specification
      <specs>FI-620</specs>.
    </step>
  </Location>
</root>

查询后返回的XML

<PI:step xmlns:PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
                        Put the <PI:material>Seat post Lug (Product Number SL-0931)</PI:material> on the <PI:material>Seat Post (Product Number SP-2981)</PI:material>.
                    </PI:step>
<PI:step xmlns:PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
                        Insert the <PI:material>Pinch Bolt (Product Number PB-6109)</PI:material> and tighten until it is secure but still able to slide up or down the post as shown in illustration <PI:diag>6</PI:diag>.
                    </PI:step>
<PI:step xmlns:PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
                        Attach the <PI:material>LL Seat (Product Number SE-T312)</PI:material> to the top of the Seat Post and tighten securely.
                    </PI:step>
<PI:step xmlns:PI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
                        Inspect per specification <PI:specs>FI-620</PI:specs>.
                    </PI:step>

（2）.value

查询离散数据

--ProductModelID=66的第1个Location元素中的LaborHours属性值
with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as PI) 

select ProductModelID,Instructions.value('(/PI:root/PI:Location/@LaborHours)[1]','decimal (5,2)') as  Location
from Production.ProductModel
where ProductModelID=66

结果如下：

ProductModelID Location
66 1.50

（3）、.modify

将LaborHours的1.5改成1.75

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as PI) 

update Production.ProductModel set Instructions.modify('replace value of (/PI:root/PI:Location/@LaborHours)[1] with 1.75') 
where ProductModelID=66

(4)、.nodes

使用.nodes可以获取XML块，并按照其在关系表中的存储方式将其拆分为多个数据行。
使用Cross Apply关键字替代Join

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as PI) 

select 
pm.ProductModelID,pmi.location.value('./@LocationID','int') as locationid,pmi.location.value('./@LaborHours','decimal(5,2)') as laborhours
 from
Production.ProductModel pm
cross apply pm.Instructions.nodes('/PI:root/PI:Location') as pmi(location);
--cross apply 将一张表分成两张表，ProductModel与ProductModel中的Instructions列返回的.nodes结果(root节点下的Location节点)

结果如下：1对多关系

（5）、exist

实例：ProductModel的Instructions字段中至少有一个step包含specs元素(区分大小写)

--命名空间声明的URL部分必须写在一行内
with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions' as PI) 

select 
ProductModelID,Instructions
 from
Production.ProductModel 
where Instructions.exist('/PI:root/PI:Location/PI:step/PI:specs')=1
--ProductModel的Instructions字段中至少有一个step包含specs元素(区分大小写)

结果如下：

二、提取XML格式的关系数据

1、For Xml 子句

告诉sql server 希望返回的是xml

(1).For Xml Raw

花费最少气力将关系行转换成XML数据元素。列名转成属性名。

实例：列出一些客户的订单，并转成XML

select sc.CustomerID,pp.LastName,pp.FirstName,soh.SalesOrderID,soh.OrderDate
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485

For Xml Raw;

直接在后面加个For Xml Raw

生成结果：

<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="44132" OrderDate="2001-09-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="45579" OrderDate="2002-03-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="46389" OrderDate="2002-06-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="47454" OrderDate="2002-09-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="48395" OrderDate="2002-12-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="49495" OrderDate="2003-03-01T00:00:00" />
<row CustomerID="29484" LastName="Achong" FirstName="Gustavo" SalesOrderID="50756" OrderDate="2003-06-01T00:00:00" />
<row CustomerID="29485" LastName="Abel" FirstName="Catherine" SalesOrderID="53459" OrderDate="2003-09-01T00:00:00" />
<row CustomerID="29485" LastName="Abel" FirstName="Catherine" SalesOrderID="58907" OrderDate="2003-12-01T00:00:00" />
<row CustomerID="29485" LastName="Abel" FirstName="Catherine" SalesOrderID="65157" OrderDate="2004-03-01T00:00:00" />
<row CustomerID="29485" LastName="Abel" FirstName="Catherine" SalesOrderID="71782" OrderDate="2004-06-01T00:00:00" />

(2)、For Xml Auto

元素名称变成了数据来源表的名称或表别名。

select sc.CustomerID,soh.OrderDate
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485

For Xml Auto;

结果如下：

<sc CustomerID="29484">
  <pp LastName="Achong" FirstName="Gustavo">
    <soh SalesOrderID="44132" OrderDate="2001-09-01T00:00:00" />
    <soh SalesOrderID="45579" OrderDate="2002-03-01T00:00:00" />
    <soh SalesOrderID="46389" OrderDate="2002-06-01T00:00:00" />
    <soh SalesOrderID="47454" OrderDate="2002-09-01T00:00:00" />
    <soh SalesOrderID="48395" OrderDate="2002-12-01T00:00:00" />
    <soh SalesOrderID="49495" OrderDate="2003-03-01T00:00:00" />
    <soh SalesOrderID="50756" OrderDate="2003-06-01T00:00:00" />
  </pp>
</sc>
<sc CustomerID="29485">
  <pp LastName="Abel" FirstName="Catherine">
    <soh SalesOrderID="53459" OrderDate="2003-09-01T00:00:00" />
    <soh SalesOrderID="58907" OrderDate="2003-12-01T00:00:00" />
    <soh SalesOrderID="65157" OrderDate="2004-03-01T00:00:00" />
    <soh SalesOrderID="71782" OrderDate="2004-06-01T00:00:00" />
  </pp>
</sc>

(3)、For Xml Explicit

可以对什么是元素、什么是属性、以及什么元素被嵌套到其他元素中，进行非常好的控制。
关于结果集，应该注意到如下几件事情：
a. 其中添加了两个特殊的元数据列（Tag和Parent）,否则，不能使用数据（它们不是来自于表的列）。
TAG 表示节点,PARENT表示节点的父节点
b. 真正的列名遵循特殊的格式。
[节点名!Tag!属性名]

c. 数据已经基于层次而被排序

实例：

select 1 as Tag,--节点
null as parent,--父节点
sc.CustomerID as [sc!1!CustomerID],--[节点名!Tag!属性名]
null as [pp!2!LastName],null as [pp!2!FirstName],null as [soh!3!SalesOrderID],null as [soh!3!OrderDate]
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
where sc.CustomerID=29484 or sc.CustomerID=29485

union
select 2,1,sc.CustomerID as [sc!1!CustomerID],pp.LastName as [pp!2!LastName],pp.FirstName as [pp!2!FirstName],null as [soh!3!OrderDate]
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485 
      
union all

select 3,2,soh.OrderDate
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485 

order by [sc!1!CustomerID],[pp!2!LastName],[pp!2!FirstName],[soh!3!SalesOrderID]
For Xml ExPlicit

不转换成XML结果

For Xml ExPlicit转成XML结果

<sc CustomerID="29484">
  <pp LastName="Achong" FirstName="Gustavo">
    <soh SalesOrderID="44132" OrderDate="2001-09-01T00:00:00" />
    <soh SalesOrderID="45579" OrderDate="2002-03-01T00:00:00" />
    <soh SalesOrderID="46389" OrderDate="2002-06-01T00:00:00" />
    <soh SalesOrderID="47454" OrderDate="2002-09-01T00:00:00" />
    <soh SalesOrderID="48395" OrderDate="2002-12-01T00:00:00" />
    <soh SalesOrderID="49495" OrderDate="2003-03-01T00:00:00" />
    <soh SalesOrderID="50756" OrderDate="2003-06-01T00:00:00" />
  </pp>
</sc>
<sc CustomerID="29485">
  <pp LastName="Abel" FirstName="Catherine">
    <soh SalesOrderID="53459" OrderDate="2003-09-01T00:00:00" />
    <soh SalesOrderID="58907" OrderDate="2003-12-01T00:00:00" />
    <soh SalesOrderID="65157" OrderDate="2004-03-01T00:00:00" />
    <soh SalesOrderID="71782" OrderDate="2004-06-01T00:00:00" />
  </pp>
</sc>

element 列作为元素添加，而不作为属性添加

select 1 as Tag,--节点
null as parent,--父节点
sc.CustomerID as [sc!1!CustomerID],--[节点名!Tag!属性名]
null as [pp!2!LastName],
null as [pp!2!FirstName],
null as [soh!3!SalesOrderID],
null as [soh!3!OrderDate!element]
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
where sc.CustomerID=29484 or sc.CustomerID=29485

union
select 2,
1,
sc.CustomerID as [sc!1!CustomerID],
pp.LastName as [pp!2!LastName],
pp.FirstName as [pp!2!FirstName],
null as [soh!3!OrderDate]
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485

union all

select 3,
2,
soh.SalesOrderID,
soh.OrderDate
from Person.Person pp
join Sales.Customer sc
on pp.BusinessEntityID=sc.PersonID
join Sales.SalesOrderHeader soh
on sc.CustomerID=soh.CustomerID
where sc.CustomerID=29484 or sc.CustomerID=29485

order by [sc!1!CustomerID],[soh!3!SalesOrderID]
For Xml ExPlicit

结果：

<sc CustomerID="29484">
  <pp LastName="Achong" FirstName="Gustavo">
    <soh SalesOrderID="44132">
      <OrderDate>2001-09-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="45579">
      <OrderDate>2002-03-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="46389">
      <OrderDate>2002-06-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="47454">
      <OrderDate>2002-09-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="48395">
      <OrderDate>2002-12-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="49495">
      <OrderDate>2003-03-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="50756">
      <OrderDate>2003-06-01T00:00:00</OrderDate>
    </soh>
  </pp>
</sc>
<sc CustomerID="29485">
  <pp LastName="Abel" FirstName="Catherine">
    <soh SalesOrderID="53459">
      <OrderDate>2003-09-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="58907">
      <OrderDate>2003-12-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="65157">
      <OrderDate>2004-03-01T00:00:00</OrderDate>
    </soh>
    <soh SalesOrderID="71782">
      <OrderDate>2004-06-01T00:00:00</OrderDate>
    </soh>
  </pp>
</sc>

xml
该指令在本质上与element指令相同。生成的相关列表表现为元素而不是属性。
只有在需要对一些特殊字符（XML保留字）进行编码的时候，才可以看到xml和element指令之间的差别。

[soh!3!OrderDate!xml]

hide 隐藏列

如：[soh!3!OrderDate!hide]

id、idref 和 idrefs
id 为元素名称唯一标识。idref 和 idrefs 外键。
打破元素间的嵌套关系、无视两个元素在文档中的位置把它们连接起来。
一对多关系(For Xml Explicit,xmldata)

cdata 字符串数据

实例：DocumentSummary是一个nvarchar(max)数据类型的字段

select 1 as Tag,null as Parent,DocumentNode as [Document!1!DocumentNode],DocumentSummary as [Document!1!cdata]

 from
Production.Document Document
where  DocumentSummary is not null
order by [Document!1!DocumentNode]
for xml explicit

<Document DocumentNode="/1/2/" cdata="It is important that you maintain your bicycle and keep it in good repair. Detailed repair and service guidelines are provided along with instructions for adjusting the tightness of the suspension fork.

" />
<Document DocumentNode="/2/2/" cdata="Guidelines and recommendations for lubricating the required components of your Adventure Works Cycles bicycle. Component lubrication is vital to ensuring a smooth and safe ride and should be part of your standard maintenance routine. Details instructions are provided for each bicycle component requiring regular lubrication including the frequency at which oil or grease should be applied.
" />
<Document DocumentNode="/3/2/" cdata="Reflectors are vital safety components of your bicycle. Always ensure your front and back reflectors are clean and in good repair. Detailed instructions and illustrations are included should you need to replace the front reflector or front reflector bracket of your Adventure Works Cycles bicycle.

" />
<Document DocumentNode="/3/3/" cdata="Detailed instructions for replacing pedals with Adventure Works Cycles replacement pedals. Instructions are applicable to all Adventure Works Cycles bicycle models and replacement pedals. Use only Adventure Works Cycles parts when replacing worn or broken components.
" />
<Document DocumentNode="/3/4/" cdata="Worn or damaged seats can be easily replaced following these simple instructions. Instructions are applicable to these Adventure Works Cycles models: Mountain 100 through Mountain 500. Use only Adventure Works Cycles parts when replacing worn or broken components.

" />

（4）、For Xml Path

(5)、OPENXML

OPENXML 通过 XML 文档提供行集视图

第四章、XML集成(定义XML数据列、XML模式集合、XML数据类型方法 .query,.value,.modify,.nodes,.exists),关系数据转成XML

一、XML数据类型

1、定义XML数据的列

2、XML模式集合

3、修改和删除XML模式集合

4、XML数据类型方法

(1).query

（2）.value

（3）、.modify

(4)、.nodes

（5）、exist

二、提取XML格式的关系数据

1、For Xml 子句

告诉sql server 希望返回的是xml

(1).For Xml Raw

花费最少气力将关系行转换成XML数据元素。列名转成属性名。

(2)、For Xml Auto

(3)、For Xml Explicit

（4）、For Xml Path

(5)、OPENXML

猜你在找的XML相关文章