程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLSERVER中XML查詢:FORXML指定RAW

SQLSERVER中XML查詢:FORXML指定RAW

編輯:關於SqlServer

    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 類型的列

    ? 1 2 3 4 5 6 7 8 9 10 11 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>

    總結

    以上對指定RAW的XML查詢就介紹完了,下一篇文章將繼續用實例介紹SQL SERVER中的XML查詢:指定AUTO查詢。

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved