指定管辖 SQLSERVER中XML查询：FORXML指定RAW

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="/2001/XMLSchema-instance">
<id>1</id>
<name>LeeWhoeeUniversity</name>
</row>
<row xmlns:xsi="/2001/XMLSchema-instance">
<id>2</id>
<name>SQLSERVER中XML查询</name>
</row>
<row xmlns:xsi="/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="/2001/XMLSchema" xmlns:sqltypes="/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="/sqlserver/2004/sqltypes" schemaLocation="/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:/lihui_830501")

结果：

<xsd:schema targetNamespace="urn:/lihui_830501" xmlns:xsd="/2001/XMLSchema" xmlns:sqltypes="/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="/sqlserver/2004/sqltypes" schemaLocation="/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:/lihui_830501" id="1" name="LeeWhoeeUniversity" />
<row xmlns="urn:/lihui_830501" id="2" name="SQLSERVER中XML查询" />
<row xmlns="urn:/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 类型的列

?