243
SQL 查詢結果為 XML
--原始數據
SELECT OrderNO,CreateDate,Username,Address FROM Whir_Order_OrderInfo
--1.AUTO模式
SELECT OrderNO,CreateDate,Username,Address
FROM Whir_Order_OrderInfo FOR XML AUTO, XMLSCHEMA
結果：
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet4" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet4" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:sqltypes="https://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="https://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="Whir_Order_OrderInfo">
<xsd:complexType>
<xsd:attribute name="OrderNO">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="32" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="CreateDate" type="sqltypes:datetime" />
<xsd:attribute name="Username">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="32" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="Address">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="256" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<Whir_Order_OrderInfo xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" OrderNO="500000411" CreateDate="2013-07-19T09:45:08.813" Username="zhangqs008" Address="天河軟件園" />
<Whir_Order_OrderInfo xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" OrderNO="500000412" CreateDate="2013-07-19T10:12:28.313" Username="xiaowanhu" Address="黃埔大道中288號擎天大廈501" />
<Whir_Order_OrderInfo xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" OrderNO="500000413" CreateDate="2013-07-19T15:46:34.753" Username="671367555" Address="大片路" />
<Whir_Order_OrderInfo xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" OrderNO="500000414" CreateDate="2013-07-19T17:33:35.853" Username="zhangqs008" Address="測試地址" />
<Whir_Order_OrderInfo xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" OrderNO="500000415" CreateDate="2013-07-19T17:40:29.783" Username="671367555" Address="上海上海市盧灣區大片路" />
--2.RAW模式
SELECT OrderNO,CreateDate,Username,Address
FROM Whir_Order_OrderInfo FOR XML RAW('Orders') , ROOT('Root')
結果：
<Root>
<Orders OrderNO="500000411" CreateDate="2013-07-19T09:45:08.813" Username="zhangqs008" Address="天河軟件園" />
<Orders OrderNO="500000412" CreateDate="2013-07-19T10:12:28.313" Username="xiaowanhu" Address="黃埔大道中288號擎天大廈501" />
<Orders OrderNO="500000413" CreateDate="2013-07-19T15:46:34.753" Username="671367555" Address="大片路" />
<Orders OrderNO="500000414" CreateDate="2013-07-19T17:33:35.853" Username="zhangqs008" Address="測試地址" />
<Orders OrderNO="500000415" CreateDate="2013-07-19T17:40:29.783" Username="671367555" Address="上海上海市盧灣區大片路" />
</Root>
SELECT OrderNO,CreateDate,Username,Address
FROM Whir_Order_OrderInfo FOR XML RAW('Orders') ,ELEMENTS, ROOT('Root')結果：
<Root>
<Orders>
<OrderNO>500000411</OrderNO>
<CreateDate>2013-07-19T09:45:08.813</CreateDate>
<Username>zhangqs008</Username>
<Address>天河軟件園</Address>
</Orders>
<Orders>
<OrderNO>500000412</OrderNO>
<CreateDate>2013-07-19T10:12:28.313</CreateDate>
<Username>xiaowanhu</Username>
<Address>黃埔大道中288號擎天大廈501</Address>
</Orders>
<Orders>
<OrderNO>500000413</OrderNO>
<CreateDate>2013-07-19T15:46:34.753</CreateDate>
<Username>671367555</Username>
<Address>大片路</Address>
</Orders>
<Orders>
<OrderNO>500000414</OrderNO>
<CreateDate>2013-07-19T17:33:35.853</CreateDate>
<Username>zhangqs008</Username>
<Address>測試地址</Address>
</Orders>
<Orders>
<OrderNO>500000415</OrderNO>
<CreateDate>2013-07-19T17:40:29.783</CreateDate>
<Username>671367555</Username>
<Address>上海上海市盧灣區大片路</Address>
</Orders>
</Root>
--3.PATH模式：@：節點屬性，/:節點層次
SELECT TOP 3 OrderNO "@OrderNO",
Username "ContactInfo/Username",
Address "ContactInfo/Address",
CreateDate "CreateDate"
FROM Whir_Order_OrderInfo FOR XML PATH('Orders'), ROOT('Root')
結果：
<Root>
<Orders OrderNO="500000411">
<ContactInfo>
<Username>zhangqs008</Username>
<Address>天河軟件園</Address>
</ContactInfo>
<CreateDate>2013-07-19T09:45:08.813</CreateDate>
</Orders>
<Orders OrderNO="500000412">
<ContactInfo>
<Username>xiaowanhu</Username>
<Address>黃埔大道中288號擎天大廈501</Address>
</ContactInfo>
<CreateDate>2013-07-19T10:12:28.313</CreateDate>
</Orders>
<Orders OrderNO="500000413">
<ContactInfo>
<Username>671367555</Username>
<Address>大片路</Address>
</ContactInfo>
<CreateDate>2013-07-19T15:46:34.753</CreateDate>
</Orders>
</Root>
更多內容，請參考：https://msdn.microsoft.com/zh-cn/library/ms178107.aspx
最後更新：2017-04-03 16:48:36