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


  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值