SQL Convert XML to Table

• 将xml nodes 属性中的值 转为table 形式

  declare @xml2 xml  set @xml2 = '<CMADatas>  <CMAData CmaName="EventCfgItem" CmaValue="sap-abc1a01" />  <CMAData CmaName="EventNode" CmaValue="" />  <CMAData CmaName="EventSource" CmaValue="" />  <CMAData CmaName="EventType" CmaValue="" />  <CMAData CmaName="EventTypeInstance" CmaValue="" /></CMADatas>' select S.value('@CmaName', 'varchar(36)') as CmaName          , S.value('@CmaValue', 'varchar(36)') as CmaName from @xml2.nodes('/CMADatas/CMAData') as T(S)

• 将 xml tag标签的中值 转为 table 格式

DECLARE @xml xml='<mssql version="10.50.4000" name="Microsoft SQL Server Developer Edition (64-bit)">  <type name="Windows 7 Main Server">    <tag KeyValue="enabled">      <boolean>true</boolean>    </tag>    <tag KeyValue="disabled">      <tag method="Read">        <type name="Windows 8 virtual pc">          <tag KeyValue="uniqueKey">            <string>SQL Server 2008</string>          </tag>          <tag KeyValue="value 8GB">            <string>SQL Server 2000</string>          </tag>        </type>      </tag>      <tag method="Read">        <type name="Windows 8 virtual pc">          <tag KeyValue="PRimaryKey">            <string>SQL Server 2008 R2 SP1</string>          </tag>          <tag KeyValue="value 8GB">            <string>SQL Server 2005 SP3</string>          </tag>        </type>      </tag>      <tag method="Write">        <type name="Windows Server 2008 virtual pc">          <tag KeyValue="primaryKey">            <string>SQL Server 2008 R2 SP2</string>          </tag>          <tag KeyValue="value 10GB">            <string>SQL Server 2012</string>          </tag>        </type>      </tag>    </tag>  </type></mssql>';SELECT    b.c.value('@version', 'varchar(100)') [version]        ,b.c.value('@name', 'varchar(200)') [Name]        ,f.c.value('@name', 'varchar(200)') [ServerName]        ,r.c.value('@KeyValue', 'varchar(50)') [KeyValue]        ,r.c.value('(boolean)[1]', 'varchar(25)') [boolean]        ,k.c.value('@method', 'varchar(25)') [method]        ,k.c.value('@name', 'varchar(25)') [virtual_pc]        ,y.c.value('@KeyValue', 'varchar(25)') [KeyValue]        ,y.c.value('(string)[1]', 'varchar(25)') [string]FROM @xml.nodes('mssql') b(c)        CROSS APPLY b.c.nodes('type')  f(c)            CROSS APPLY f.c.nodes('tag')  r(c)                CROSS APPLY f.c.nodes('tag/tag')  k(c)        --            CROSS APPLY f.c.nodes('tag/tag/type') t(c)                        CROSS APPLY f.c.nodes('tag/tag/type/tag')  y(c);