How to Shred XML

Question

Hi i have a xml... XML is in this format

DECLARE @p_xml xml;
SET @p_xml = N'
<PlaybookTemplate
  ID="72afb3f6-d369-4c32-98d1-0aec57f9d63e" >
  <Stages>
    <Stage
      ID="ACA9161A-6EAD-459E-976A-74E150E4744E">
      <StagePlays>
        <StagePlay
          ID="F050DB08-DDF2-4A98-B373-48E9B74F7751"
          Ordinal="0">
          <PlayTemplate ID="177E44E4-9B67-4130-AD2E-EC2C81737467" />
        </StagePlay>
        <StagePlay
          ID="5C5B9B28-D1CB-42B2-B3AB-314634371B3D"
          Ordinal="1000000">
          <PlayTemplate ID="7543CC31-083E-46DD-B05A-B61A6CEA9B30" />
        </StagePlay>
        <StagePlay
          ID="3DC9DFB8-35F0-40B3-ACC8-3140A1FC5719"
          Ordinal="2000000">
          <PlayTemplate ID="F95FABDC-178A-437D-A118-310B94A936FF" />
        </StagePlay>
      </StagePlays>
    </Stage>
    <Stage
      ID="1D246605-7F9D-4425-9205-040BA9D65328" >
      <StagePlays>
        <StagePlay
          ID="3EA15884-DA8B-4643-81C7-0335D194689C"
          Ordinal="0">
          <PlayTemplate ID="C93843D2-06C6-4D97-8069-3330E88A171E" />
        </StagePlay>
        <StagePlay
          ID="0A3C9D02-8E9F-4467-ADFF-A1C7C1CAD3F7"
          Ordinal="1000000">
          <PlayTemplate ID="8EAF1C50-B77F-480C-8504-6D2DF5070953" />
        </StagePlay>
        <StagePlay
          ID="80E5F975-E44F-4582-B8E3-85CABBB88D81"
          Ordinal="2000000">
          <PlayTemplate ID="81C204C4-4BBA-4467-990F-C2E685650307" />
        </StagePlay>
      </StagePlays>
    </Stage>
    <Stage
      ID="2C67C16B-A978-4F36-91AB-D2A94FE62FBD" >
    </Stage>
    <Stage
      ID="543F4D4A-5B85-467C-8149-9301F5A4F3D2" >
    </Stage>
  </Stages>
</PlaybookTemplate>
';

How to shred this?

Answer 1

check these nice tutorials

http://www.techrepublic.com/article/shred-xml-data-with-xquery-in-sql-server-2005/6140404

and best way to do this

http://stackoverflow.com/questions/61233/the-best-way-to-shred-xml-data-into-sql-server-database-columns

Answer 2

Try this:

DECLARE @p_xml XML;
SET @p_xml = N'
 <PlaybookTemplate
   ID="72afb3f6-d369-4c32-98d1-0aec57f9d63e" >
   <Stages>
     <Stage
       ID="ACA9161A-6EAD-459E-976A-74E150E4744E">
       <StagePlays>
         <StagePlay
           ID="F050DB08-DDF2-4A98-B373-48E9B74F7751"
           Ordinal="0">
           <PlayTemplate ID="177E44E4-9B67-4130-AD2E-EC2C81737467" />
         </StagePlay>
         <StagePlay
           ID="5C5B9B28-D1CB-42B2-B3AB-314634371B3D"
           Ordinal="1000000">
           <PlayTemplate ID="7543CC31-083E-46DD-B05A-B61A6CEA9B30" />
         </StagePlay>
         <StagePlay
           ID="3DC9DFB8-35F0-40B3-ACC8-3140A1FC5719"
           Ordinal="2000000">
           <PlayTemplate ID="F95FABDC-178A-437D-A118-310B94A936FF" />
         </StagePlay>
       </StagePlays>
     </Stage>
     <Stage
       ID="1D246605-7F9D-4425-9205-040BA9D65328" >
       <StagePlays>
         <StagePlay
           ID="3EA15884-DA8B-4643-81C7-0335D194689C"
           Ordinal="0">
           <PlayTemplate ID="C93843D2-06C6-4D97-8069-3330E88A171E" />
         </StagePlay>
         <StagePlay
           ID="0A3C9D02-8E9F-4467-ADFF-A1C7C1CAD3F7"
           Ordinal="1000000">
           <PlayTemplate ID="8EAF1C50-B77F-480C-8504-6D2DF5070953" />
         </StagePlay>
         <StagePlay
           ID="80E5F975-E44F-4582-B8E3-85CABBB88D81"
           Ordinal="2000000">
           <PlayTemplate ID="81C204C4-4BBA-4467-990F-C2E685650307" />
         </StagePlay>
       </StagePlays>
     </Stage>
     <Stage
       ID="2C67C16B-A978-4F36-91AB-D2A94FE62FBD" >
     </Stage>
     <Stage
       ID="543F4D4A-5B85-467C-8149-9301F5A4F3D2" >
     </Stage>
   </Stages>
 </PlaybookTemplate>
 ';
 
--SELECT @p_xml
 
SELECT 
	s.c.value('@ID', 'UNIQUEIDENTIFIER') StageID,
	sp.c.value('@ID', 'UNIQUEIDENTIFIER') StagePlayID,
	sp.c.value('@Ordinal', 'INT') AS Ordinal,
	sp.c.value('@ID', 'UNIQUEIDENTIFIER') AS PlayTemplateID
FROM @p_xml.nodes('PlaybookTemplate/Stages/Stage') s(c)
	OUTER APPLY s.c.nodes('StagePlays/StagePlay') sp(c)
 		OUTER APPLY sp.c.nodes('PlayTemplate') pt(c)