SQL 查询结果为 XML

 SELECT * FROM  [PE_ContentManage_Article] 

 

 

 

    select * from   [siteazure.sgp.local.cn.3000].[dbo].[PE_ContentManage_Article] for xml raw('Article') ,elements, root('Articles')

 

  select SourceId,NodeId, ContentId,Title from   PE_ContentManage_Article where ContentId <3  for xml auto, xmlschema

<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet8" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet8" 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="PE_ContentManage_Article">
    <xsd:complexType>
      <xsd:attribute name="SourceId" type="sqltypes:int" use="required" />
      <xsd:attribute name="NodeId" type="sqltypes:int" />
      <xsd:attribute name="ContentId" type="sqltypes:int" use="required" />
      <xsd:attribute name="Title" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="300" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<PE_ContentManage_Article xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" SourceId="0" NodeId="4" ContentId="1" Title="中国地理概况" />
<PE_ContentManage_Article xmlns="urn:schemas-microsoft-com:sql:SqlRowSet8" SourceId="0" NodeId="4" ContentId="2" Title="地形" />

select SourceId,NodeId, ContentId,Title from   PE_ContentManage_Article where ContentId <10  for xml raw('Article'), root('Articles')

<Articles>
  <Article SourceId="0" NodeId="4" ContentId="1" Title="中国地理概况" />
  <Article SourceId="0" NodeId="4" ContentId="2" Title="地形" />
  <Article SourceId="0" NodeId="4" ContentId="3" Title="气候" />
  <Article SourceId="0" NodeId="4" ContentId="4" Title="自然资源" />
  <Article SourceId="0" NodeId="4" ContentId="5" Title="区域地理" />
  <Article SourceId="0" NodeId="6" ContentId="6" Title="中国人口和计划生育" />
  <Article SourceId="0" NodeId="8" ContentId="7" Title="38项入选非遗名录名单" />
  <Article SourceId="0" NodeId="8" ContentId="8" Title="中国文物保护状况" />
  <Article SourceId="0" NodeId="13" ContentId="9" Title="外汇管理" />
</Articles>

select ContentId "@ContentId",
SourceId "Details/SourceId",
NodeId "Details/NodeId",
Title "Details/Title"
from PE_ContentManage_Article where ContentId <10 for xml path('Article'), root('Articles')

<Articles>
  <Article ContentId="1">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>4</NodeId>
      <Title>中国地理概况</Title>
    </Details>
  </Article>
  <Article ContentId="2">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>4</NodeId>
      <Title>地形</Title>
    </Details>
  </Article>
  <Article ContentId="3">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>4</NodeId>
      <Title>气候</Title>
    </Details>
  </Article>
  <Article ContentId="4">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>4</NodeId>
      <Title>自然资源</Title>
    </Details>
  </Article>
  <Article ContentId="5">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>4</NodeId>
      <Title>区域地理</Title>
    </Details>
  </Article>
  <Article ContentId="6">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>6</NodeId>
      <Title>中国人口和计划生育</Title>
    </Details>
  </Article>
  <Article ContentId="7">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>8</NodeId>
      <Title>38项入选非遗名录名单</Title>
    </Details>
  </Article>
  <Article ContentId="8">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>8</NodeId>
      <Title>中国文物保护状况</Title>
    </Details>
  </Article>
  <Article ContentId="9">
    <Details>
      <SourceId>0</SourceId>
      <NodeId>13</NodeId>
      <Title>外汇管理</Title>
    </Details>
  </Article>
</Articles>