SQL Convert XML to Table

 declare @ set @ = '<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 @.nodes('/CMADatas/CMAData') as T(S)

DECLARE @ ='<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 @.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);

原标题：SQL Convert XML to Table

关键词：sql