sql xml 标签

SELECT <column list>

[ …… ]

[ FOR XML { RAW | AUTO | EXPLICIT [, XMLDATA ] [ , ELEMENT ] [ , BINARY base64 ] | PATH } ]

[ OPTION (< query hit > , [ , …… ] ) ]

 

RAW ：将结果集中的每一行数据都作为单个数据元素返回，元素名为row。所有列做为row的属性，即使连接多个表，也一样。

SELECT * FROM  A  JOIN B ON A.ID = B.ID FOR XML RAW

结果<row id=”id” name=”” />样的形式

AUTO ：把每个元素通过表名或数据源表的别名来标记。如果输出来自查询中多个表，每个表的数据将划分为单独的，嵌套的表。此时，而且想数据作为元素而非属性表示，那么也将支持另外一个选项ELEMENTS。

SELECT A.id , B.Name FROM  A  JOIN B ON A.ID = B.ID FOR XML AUTO

结果为

<A id=”id”>

  <B Name=”name” />

  <B Name=”name3” />

</A>

的形式

EXPLICIT ：对查询进行格式化的最为复杂的选项，最终结果也会对XML的样式有高度的控制能力。PATH选项很大程度上代替了这个选项，EXPLICIT也只是为了向后兼容。

PATH ：SQL SERVER 2005中增加，用来提供EXPLICIT的灵活层次，有着便于使用的格式。

除了4种格式选项之外，还有5种其他可选的参数来进一步修改SQL SERVER提供给XML查询的输出。

XMLDATA ：它告诉SQL SERVER对结果应用XML模式，XML模式定义了XML数据的结构以及规则。

ELEMENT ：只有在AUTO时有用，希望数据中的列做为嵌套元素而不是属性返回。

BINARY BASE64 ：将任何二进制的数据列用base64格式编码。AUTO选项，暗示使用了这个参数，其它的选项要显示使用。

TYPE ：它告诉SQL SERVER返回XML数据类型而不是默认的Unicode字符类型的结果报表。

ROOT ：让SQL SERVER添加根节点，可提供名称或使用默认的名称root。

 

EXPLICIT选项可以定义层次结构的每一个层次以及每个层次的样式。为了定义层次结构，需要建立所谓的通用表(universal table)。许多方面，通用表和SQL SERVER产生的其它表一样，通常通过每次使用UNION语句结合一个层次来产生结果，与传统的结果集最大的区别在于必须在结果集中提供足够的元数据。

首先它有两个特殊的元数据列，Tag和Parent，但是它们不和数据相关，它们定义了每一行元素层次结构的关系列。Tag列中表示在那个层次，Parent提供引用信息，指出在层次结构的父层次是哪个。

如   Tag     Parent

1        NULL           //最高层次，父为NULL

2        1               //  第二层次 ，父为 1

其次，列的名称遵循特殊的格式（提供额外的元数据）

命名格式 ：<element name>!<tag>![<attribute>][!element | hide | ID | IDREF | IEREFS | xml | xmltext | cdata]

我们可以看到属性名是可选的，根据最后的参数，属性可分为3类：

1，  禁止的，属性必须为空白，如参数为CDATA时

2，  可选的，

3，  必须的，对于elements和xml参数

 

想要如AUTO的结果

<A id=”id”>

  <B Name=”name” />

  <B Name=”name3” />

</A>

的形式

 

SELECT

1 as Tag , NULL as Parent , A.id as [ A!1!id] , NULL as [B!2!Name]

FROM A

WHERE A.id = ‘id’

UNION ALL

SELECT

2 , 1 , A.id , B.Name

FROM A JOIN B ON A.id = B.id

WHERE A.id = ‘id’

FOR XML EXPLICIT

 

从上可以看到，如果想让id做为B的属性，只须改为 [B!2!id] 配置项就可

参数

ELEMENT ：如[B!2!name!element]，这样name就会做为B的子元素出现

XML ： 和ELEMENT类似，区别只是在存在特殊字符时，用element时会对特殊字符进行编码。

hide ：在最后输出的XML时隐藏该列。如不想让用户看到，但是逻辑上要用到这个列（排序等）

id，idref和idrefs ：要在使用XMLDATA选项或者已有合适声明的其他模式进行验证，否则这三个选项没用。id就相当于数据库的主键，idref就相当于外键，inrefs可引用多个列，值必须与现有的id值相匹配。FOR XML EXPLICIT ，XMLDATA

xmltext ，期望列的内容为XML，并尝试将它作为创建的XML文档的组成部分进行插入。遵循以下规则：

1，  如果插入的XML是良好格式的，那么去除根节点，但保留该元素的属性。

2，  如果XMLTEXT指令时没有指定属性名称，那么去除的元素的保留的属性会添加给该元素，名字仍然用原来的，如果有冲突，就去掉保留的这个属性。

3，  任何嵌套在去除元素内的元素会成为组合元素的嵌套元素

4，  如果XMLDATA指令提供了属性名，那么保留的数据会放入提供名称的元素内，新的元素成为发出指令的元素的子节点。

5，  如果结果XML不是良好的XML格式，那么就没有定义的行为。

CDATA ：它代表字符数据，XML完全忽略标记为CDATA部分包含的所有内容。

如  A.Content as [A!12!!CDATA]

生成的XML ：<A>  <![CDATA[ Conten内容  ]]></A>

 

PATH

PATH更加简单，但功能比EXPLICIT强大，可得到更复杂的XML输出。不过要用到XPATH。PATH对待引用的数据取决于许多的规则，其中包括了是否对列进行了命名，如果使用了别名，就用别名当做它的名称。如果列没有命名，就直接把值插入。

如

SELECT A.ID , COUNT(B.bID) FROM A INNER JOIN B ON A.ID = B.ID FOR XML PATH

结果为<row> <ID>ffff</ID> 7 </row> ，看到COUNT(B.bID)的值没有元素名，对于已命名的列如A.ID，它的名称就会成为元素名，

属性SELECT A.ID AS ‘@AID’ FROM …… FOR XML PATH

结果为<row AID=”ffff” />

SELECT A.aID , COUNT(B.bID) AS ‘@BID’ ……

这样会出错，因为SQL SERVER不知道把这个属性加给元素aID，还是元素row

如果都改为属性，可以。

SELECT A.AID , COUNT(B.BID) AS ‘AID/BID’

这样B.BID就会成为AID的子元素<row><AID>ffff<BID>4</BID></AID></row>

如SELECT A.AID , COUNT(B.BID) AS ‘AID/@BID’

也会出错

但是改为SELECT COUNT(B.BID) AS ‘AID/@BID’ , A.AID 这样就会正确

结果为<row><AID BID=”5” />ffff</AID></row>

因为创建元素之前就必须把属性创建好

 

OPENXML

SQL打开XML的字符串并使用它做为数据源用预期的表的格式来表示它，与FOR XML正好相反。

OPENXML是一个行集合rowset函数，它会像其他行集合函数(OPENQUERY和OPENROWSET)那样打开字符串。它可以连接一个XML文档，或甚至通过使用INSERT …… SELECT或SELECT INTO把它作为输入数据的数据源，主要区别在于它需要用到几个系统的存储过程以准备文档并在用完它之后释放内存。

为了建立文档，可以使用sp_xml_preparedocument存储过程，它会把字符串放入内存中并且为了最优的查询性能而对文档进行分析处理。XML文档会一直存在内存中，直到显式的删除它或终止调用sp_xml_preparedocument的连接。

sp_xml_preparedocument @hdoc = <integer variable> OUTPUT , [ , @xmltext = < character date > ] [ , @xpath_namespace = < url to a namespace > ]

@hdoc，句柄是一个指向内存块的指针，SQL分析XML文档之后为我们保持的句柄，是一个输出变量，这个变量在存储过程后返回。

@xmltext，要分析和处理的真正的XML。

@xpath_namespace，需要正确操作任何引用XML的命名空间。

调用完存储过程并保存完句柄后，就可以使用OPENXML，

OPENXML(<handle> , <XPath to base node> [, <mapping flags >] )

[ WITH (<Schema Declaration > | <table name>) ]

handle，调用sp_xml_preparedocument的输出参数，整数值。

XPath to base node ：为节点提供路径，它将作为所有查询的起始点。模式声明通过导航到相关设置的基节点来引用XML文档的所有部分。

映射标志决定我们使用元素还是属性。0，和1相同，除了不能和其它的标志结合，默认选项。1 ，除非和2结合，否则只会使用属性，若没有为属性指定名称，返回null，比2优先，如果XPath找到了相同的名称的属性和元素，将使用属性。2，除非和1相结合否则只使用元素，没有为元素指定名称，就返回null，和1结合时，会先使用属性，然后是元素，没有时就null。8，可以和1、2结合，使用的数据不会复制到溢出属性@mp:xmltext中，如果不准备使用MetaProperties，可能大部分的时候也不会这么做，建议使用这个选项，会占用较少的操作开销。

模式和表

WITH

(

<column name> <data type> [{<Column XPath> | <MetaProperty>}]

……

)

column name检索的的属性或元素的名称，

data type是数据类型，

Column XPath，以OPENXML函数指定的结点为起始点，是为你找到想要的列节点，是否使用属性取决于上面说明的标记参数

MetaProperties , 是特殊变量的集合，可以在OPENXML查询中引用它们，它描述了XML DOM的各个方面，要使用它们要把它们用单引号把它们围起来放入列的XPath中：

@mp:id , @mp:parentid , @mp:localname , @mp:parentlocalname , @mp:namespaceuri , @mp:parentnamespaceuri , @mp:prefix , @mp:prev , @mp:xmltext

 

清除文档

sp_xml_removedocument [hdoc = ] <handle of XML doc>

 

示例：

DECLARE @idoc int

DECLARE @xmldoc nvarchar(2000)

SET @xmldoc = ‘<ROOT><Shipper ShipperID=”100” CompanyName=”Bs” /><Shipper ShipperID=”101” CompanyName=”Cm” /></ROOT>’

EXEC sp_xml_preparedocument @idoc OUTPUT , @xmldoc

SET IDENTITY_INSERT table1 ON

INSERT INTO table1 (ShipperID , CompanyName ) SELECT * FROM OPENXML ( @idoc , ‘/ROOT/Shipper’ , 0 ) WITH(ShipperID int , CompanyName nvarchar(40) )

SET IDENTITY_INSERT table1 OFF

EXEC sp_xml_removedocument @idoc

如果比较复杂的XML文档，可用 ../@ProductID  可定位到结点，还可以用@mp:prev等