xml之浅谈path和raw

     -- 转帖请注明--
-- 2008/12/29/pm 12:10 徐王锦--
-- xml 浅谈 path 和 raw

if object_id (N ' test ' , ' U ' ) is not null
drop table test
go
select *
into test
  from ( select id = 1 ,name = ' 金庸 '
        union all
        select id = 2 ,name = ' 古龙 ' )x
if object_id (N ' test2 ' , ' U ' ) is not null
drop table test2
go
select *
into test2
  from ( select id = 1 ,oid = 1 ,name = ' 笑傲江湖 '
        union all
        select id = 2 ,oid = 1 ,name = ' 书剑恩仇录 '
        union all
        select id = 3 ,oid = 2 ,name = ' 大地飞鹰 '
        union all
        select id = 4 ,oid = 2 ,name = ' 圆月弯刀 ' )x



-- raw,path都是按照行来生成元素的
select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw
/*
<row id="1" author="金庸" book_name="笑傲江湖" />
<row id="2" author="金庸" book_name="书剑恩仇录" />
<row id="3" author="古龙" book_name="大地飞鹰" />
<row id="4" author="古龙" book_name="圆月弯刀" />
*/

select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml path
/*
<row>
  <id>1</id>
  <author>金庸</author>
  <book_name>笑傲江湖</book_name>
</row>
<row>
  <id>2</id>
  <author>金庸</author>
  <book_name>书剑恩仇录</book_name>
</row>
<row>
  <id>3</id>
  <author>古龙</author>
  <book_name>大地飞鹰</book_name>
</row>
<row>
  <id>4</id>
  <author>古龙</author>
  <book_name>圆月弯刀</book_name>
</row>
*/


-- 指定 row 别名
select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' )
/*
<r id="1" author="金庸" book_name="笑傲江湖" />
<r id="2" author="金庸" book_name="书剑恩仇录" />
<r id="3" author="古龙" book_name="大地飞鹰" />
<r id="4" author="古龙" book_name="圆月弯刀" />
*/

select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml path( ' r ' )

/*
<r>
  <id>1</id>
  <author>金庸</author>
  <book_name>笑傲江湖</book_name>
</r>
<r>
  <id>2</id>
  <author>金庸</author>
  <book_name>书剑恩仇录</book_name>
</r>
<r>
  <id>3</id>
  <author>古龙</author>
  <book_name>大地飞鹰</book_name>
</r>
<r>
  <id>4</id>
  <author>古龙</author>
  <book_name>圆月弯刀</book_name>
</r>
*/
共同点：
都是按照 < row > 来限定节点的 换言之在已查询的行记录
有几行就有几个row 节点
row 节点名 可更改 默认为 row
raw path 都可指定跟节点 root()和 缺少相应的属性、节点或元素 elements xsinil

区别
raw 可以指定 elements
path 则不能 因为他本身就已把 < row > 里的属性 拆分成 < row > 的子元素.
raw 可以在 elements 下 具有重复的 子元素 path 则不能 只取相同名列的第一个

-- 1 raw elements
select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' ),elements
/*
<r>
  <id>1</id>
  <author>金庸</author>
  <book_name>笑傲江湖</book_name>
</r>
<r>
  <id>2</id>
  <author>金庸</author>
  <book_name>书剑恩仇录</book_name>
</r>
<r>
  <id>3</id>
  <author>古龙</author>
  <book_name>大地飞鹰</book_name>
</r>
<r>
  <id>4</id>
  <author>古龙</author>
  <book_name>圆月弯刀</book_name>
</r>
*/
-- 2 raw elements xsinil
select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' ),elements xsinil
/*
<r xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>1</id>
  <author>金庸</author>
  <book_name>笑傲江湖</book_name>
</r>
<r xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>2</id>
  <author>金庸</author>
  <book_name>书剑恩仇录</book_name>
</r>
<r xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>3</id>
  <author>古龙</author>
  <book_name>大地飞鹰</book_name>
</r>
<r xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <id>4</id>
  <author>古龙</author>
  <book_name>圆月弯刀</book_name>
</r>
*/
-- 3 raw 在 elements | elements xsinil 关于重名的列
-- raw 没指定 elements 则在 <row>节点里的属性却为唯一
select b.id,a.name , b.name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' ),elements
/*
<r>
  <id>1</id>
  <name>金庸</name>
  <name>笑傲江湖</name>
</r>
<r>
  <id>2</id>
  <name>金庸</name>
  <name>书剑恩仇录</name>
</r>
<r>
  <id>3</id>
  <name>古龙</name>
  <name>大地飞鹰</name>
</r>
<r>
  <id>4</id>
  <name>古龙</name>
  <name>圆月弯刀</name>
</r>
*/

错误的写法

select b.id,a.name , b.name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' )

/*
消息 6810，级别 16，状态 1，第 1 行
列名 'name' 重复。不能对同一 XML 标记多次生成相同的属性。
*/
-- raw path 都可指定跟节点 root()

select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml raw ( ' r ' ),root( ' root ' )
/*
<root>
  <r id="1" author="金庸" book_name="笑傲江湖" />
  <r id="2" author="金庸" book_name="书剑恩仇录" />
  <r id="3" author="古龙" book_name="大地飞鹰" />
  <r id="4" author="古龙" book_name="圆月弯刀" />
</root>
*/

select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
   for xml path( ' r ' ),root( ' root ' )
/*
<root>
  <r>
    <id>1</id>
    <author>金庸</author>
    <book_name>笑傲江湖</book_name>
  </r>
  <r>
    <id>2</id>
    <author>金庸</author>
    <book_name>书剑恩仇录</book_name>
  </r>
  <r>
    <id>3</id>
    <author>古龙</author>
    <book_name>大地飞鹰</book_name>
  </r>
  <r>
    <id>4</id>
    <author>古龙</author>
    <book_name>圆月弯刀</book_name>
  </r>
</root>
*/

-- 入门 path
-- 引之联机文档
PATH 模式还是一种用于引入附加嵌套来表示复杂属性的较简单的方法。尽管您可以使用 FOR XML EXPLICIT 模式查询从行集构造这种 XML，但 PATH 模式为可能很麻烦的 EXPLICIT 模式查询提供了一种较简单的替代方法。通过 PATH 模式，以及用于编写嵌套 FOR XML 查询的功能和返回 xml 类型实例的 TYPE 指令
-- 1 构造自己想要的xml 格式
select b.id,a.name as author, b.name as book_name
from test as a inner join test2 as b
  on a.id = b.oid
1     金庸    笑傲江湖
2     金庸    书剑恩仇录
3     古龙    大地飞鹰
4     古龙    圆月弯刀
-- eg
我要已 author 作为 < raw > 属性 子元素为 oid ,id 为 oid 的属性 book_name 为 < oid > 的元素


select a.name as " @author ", b.id as "oid / @id ", b.name as "oid / book_name"
from test as a inner join test2 as b
  on a.id = b.oid
  for xml path
/*
<row author="金庸">
  <oid id="1">
    <book_name>笑傲江湖</book_name>
  </oid>
</row>
<row author="金庸">
  <oid id="2">
    <book_name>书剑恩仇录</book_name>
  </oid>
</row>
<row author="古龙">
  <oid id="3">
    <book_name>大地飞鹰</book_name>
  </oid>
</row>
<row author="古龙">
  <oid id="4">
    <book_name>圆月弯刀</book_name>
  </oid>
</row>
*/
如果我要在 上面的基础上只根据 < row > 的元素 分行级又如何？
/*
<row author="金庸">
   <oid id="1">
    <book_name>笑傲江湖</book_name>
   </oid>
   <oid id="2">
    <book_name>书剑恩仇录</book_name>
   </oid>
</row>
<row author="古龙">
  <oid id="3">
    <book_name>大地飞鹰</book_name>
  </oid>
  <oid id="4">
    <book_name>圆月弯刀</book_name>
  </oid>
</row>
*/

-- 待续
-- 2008/12/29/am 1:13 徐王锦--