在SQL SERVER中,XML查詢可以指定RAW,AUTO,EXPLICIT,PATH。本文用一些實例介紹SQL SERVER中指定RAW的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可選,不會影響結果,只是影響數據類型。
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返回描述文檔結構的 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" />
通過指定 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。
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的情況類同。
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>
結果:
<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>
以上對指定RAW的XML查詢就介紹完了,下一篇文章將繼續用實例介紹SQL SERVER中的XML查詢:指定AUTO查詢。