SQL 查询结果为 XML

2021-12-11 03:03:01

--原始数据

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="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="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>

更多内容，请参考：http://msdn.microsoft.com/zh-cn/library/ms178107.aspx

码农公寓

相关文章