查詢語句搭配FOR xml子句獲得結構描述(schema)與xml資料的查詢結果

 

查詢語句搭配FOR　xml子句獲得結構描述(schema)與xml資料的查詢結果
查詢語句:
SELECT top(3)  [EXE_ID]
      ,[EXE_NAME]
      ,[EXE_PSSWORD]
     ,[EXE_ADDRESS]
      ,[EXE_NUMBER]
  FROM [EXE].[dbo].[EXE_UserInfo] as users
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="users">
    <xsd:complexType>
      <xsd:attribute name="EXE_ID" type="sqltypes:int" use="required" />
      <xsd:attribute name="EXE_NAME" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="50" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="EXE_PSSWORD" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="50" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="EXE_ADDRESS" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="100" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
      <xsd:attribute name="EXE_NUMBER" use="required">
        <xsd:simpleType>
          <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1028" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
            <xsd:maxLength value="18" />
          </xsd:restriction>
        </xsd:simpleType>
      </xsd:attribute>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<users xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" EXE_ID="10002" EXE_NAME="LiuXiaoXiangaa" EXE_PSSWORD="password" EXE_ADDRESS="蘆洲市" EXE_NUMBER="097544568784013461" />
<users xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" EXE_ID="10003" EXE_NAME="WanghaiTao" EXE_PSSWORD="password" EXE_ADDRESS="山東省" EXE_NUMBER="450903198806013461" />
<users xmlns="urn:schemas-microsoft-com:sql:SqlRowSet4" EXE_ID="10004" EXE_NAME="WangLinHui" EXE_PSSWORD="password" EXE_ADDRESS="成都市" EXE_NUMBER="456789123456789564" />