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);