SQL SERVER中XML查询:FOR XML指定RAW
来源:互联网 发布:在线算法测试网站 编辑:程序博客网 时间:2024/05/21 15:46
SQL SERVER中XML查询:FOR XML指定RAW
前言
在SQL SERVER中,XML查询可以指定RAW,AUTO,EXPLICIT,PATH。本文用一些实例介绍SQL SERVER中指定RAW的XML查询。
基础FOR XML查询
看实例:
with TestXml
as
(
select 1 as id,'LeeWhoeeUniversity' as name
union all
select 2,'SQLSERVER中XML查询'
union all
select 3 ,'FOR XML'
)
select id,name from testxml for xml raw,type
运行后结果:
<row id="1" name="LeeWhoeeUniversity" />
<row id="2" name="SQLSERVER中XML查询" />
<row id="3" name="FOR XML" />
红色字体type可选,不会影响结果,只是影响数据类型。
指定 ELEMENTS:
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,elements
注意,第三行值改为NULL值进行测试。
结果:
<row>
<id>1</id>
<name>LeeWhoeeUniversity</name>
</row>
<row>
<id>2</id>
<name>SQLSERVER中XML查询</name>
</row>
<row>
<id>3</id>
</row>
元素name在第三行没有出现,因为是NULL值。
但是我们可以用XSINIL生成NULL值的name元素。
如:
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,elements XSINIL
运行结果:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>1</id>
<name>LeeWhoeeUniversity</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>2</id>
<name>SQLSERVER中XML查询</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>3</id>
<name xsi:nil="true" />
</row>
使用XMLDATA和XMLSCHEMA
XMLDATA返回描述文档结构的 XML-DATA 架构。
如:
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLDATA
结果:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="row" content="empty" model="closed">
<AttributeType name="id" dt:type="i4" />
<AttributeType name="name" dt:type="string" />
<attribute type="id" />
<attribute type="name" />
</ElementType>
</Schema>
<row xmlns="x-schema:#Schema2" id="1" name="LeeWhoeeUniversity" />
<row xmlns="x-schema:#Schema2" id="2" name="SQLSERVER中XML查询" />
<row xmlns="x-schema:#Schema2" id="3" />
XML SCHEMA
通过指定 XMLSCHEMA 选项,您可以针对结果请求 XSD 架构:
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA
结果:
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="row">
<xsd:complexType>
<xsd:attribute name="id" type="sqltypes:int" use="required" />
<xsd:attribute name="name">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="12" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="1" name="LeeWhoeeUniversity" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="2" name="SQLSERVER中XML查询" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="3" />
您可以将目标命名空间 URI 指定为 FOR XML 中 XMLSCHEMA 的可选参数。
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA ('urn:http://blog.csdn.net/lihui_830501')
结果:
<xsd:schema targetNamespace="urn:http://blog.csdn.net/lihui_830501" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="row">
<xsd:complexType>
<xsd:attribute name="id" type="sqltypes:int" use="required" />
<xsd:attribute name="name">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="12" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="1" name="LeeWhoeeUniversity" />
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="2" name="SQLSERVER中XML查询" />
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="3" />
检索二进制数据
像XMLDATA一样,在SQL中指定BINARY BASE64。
重命名 <row> 元素
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw ('myrow')
结果:
<myrow id="1" name="LeeWhoeeUniversity" />
<myrow id="2" name="SQLSERVER中XML查询" />
<myrow id="3" />
指定ELEMENTS的情况类同。
为 FOR XML 生成的 XML 指定根元素
with TestXml
as
(
select 1 as id,N'LeeWhoeeUniversity' as name
union all
select 2,N'SQLSERVER中XML查询'
union all
select 3 ,null
)
select id,name from testxml for xml raw,root('myroot')
结果:
<myroot>
<row id="1" name="LeeWhoeeUniversity" />
<row id="2" name="SQLSERVER中XML查询" />
<row id="3" />
</myroot>
查询 XML 类型的列
declare @xml table(xid int,xname varchar(50),xmlcol xml);insert into @xml select 1,'第一行','<myroot> <row id="1" name="LeeWhoeeUniversity" /> <row id="2" name="SQLSERVER中XML查询" /> <row id="3" /></myroot>'insert into @xml select 2,'第二行','<myroot> <row id="4" name="XML数据" /> <row id="5" name="FOR XML指定RAW" /></myroot>'select xid,xname,xmlcol.query('/myroot') from @xml for xml raw
结果:
<row xid="1" xname="第一行">
<myroot>
<row id="1" name="LeeWhoeeUniversity" />
<row id="2" name="SQLSERVER中XML查询" />
<row id="3" />
</myroot>
</row>
<row xid="2" xname="第二行">
<myroot>
<row id="4" name="XML数据" />
<row id="5" name="FOR XML指定RAW" />
</myroot>
</row>
总结
- SQL SERVER中XML查询:FOR XML指定RAW
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL SERVER中XML查询:FOR XML指定EXPLICIT
- SQL SERVER中XML查询:FOR XML指定PATH
- 关于XML类型,请参考:http://blog.csdn.net/leewhoee/article/details/8571286
- 关于XML索引,请参考:http://blog.csdn.net/leewhoee/article/details/8579743
- SQL SERVER中XML查询:FOR XML指定RAW
- SQL SERVER中XML查询:FOR XML指定RAW
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL SERVER中XML查询:FOR XML指定EXPLICIT
- SQL SERVER中XML查询:FOR XML指定PATH
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL查询 FOR XML [RAW|AUTO|EXPLICIT]
- SQL查询 FOR XML [RAW|AUTO|EXPLICIT]
- SQL查询 FOR XML [RAW|AUTO|EXPLICIT]
- SQL Server2005中使用XML-FOR XML(RAW模式查询)
- SQL Server XML(For Xml Path、Raw/Auto、explicit、XPah、Xquery xml查询.value(),exists(),nodes())
- SQL Server XML基础学习<2>之--FOR XML AUTO/RAW
- 用RAW模式查询创建XML(SQL Server 与 XML 笔记)
- SQL Server FOR XML
- SQL SERVER中 用FOR XML PATH将查询结果以XML输出 然后应用于 sql 合并字段功能介绍
- FOR XML (AUTO,RAW)
- SQl 2005 For XMl 简单查询(Raw,Auto,Path模式)(1)
- SQL SERVER中CUME_DIST和PERCENT_RANK函数
- SQL SERVER中LEAD和LAG函数
- SQL SERVER中FIRST_VALUE和LAST_VALUE
- SQL SERVER中PERCENTILE_CONT和PERCENTILE_DISC
- SQL SERVER中GROUPING SETS,CUBE,ROLLUP
- SQL SERVER中XML查询:FOR XML指定RAW
- Stack_Queue 固定容量的栈组SetOfStacks @CareerCup
- 循环不变性(loop invariant)-证明算法的正确性的一种方法
- SQL SERVER中XML查询:FOR XML指定AUTO
- SQL SERVER中XML查询:FOR XML指定EXPLICIT
- SQL SERVER中XML查询:FOR XML指定PATH
- 一个hello程序的android内核模块编译方法及在模拟器中进行测试结论
- all kinds of containers
- Stack_Queue 汉诺塔Hanoi问题 @CareerCup