This is an example of how you can shred the XML when the number of elements you want to query are unknown - you will need to use dynamic pivot on this output using the RN column to get the data in the format you describedDECLARE @x XML = '<root> <Ticket TicketID="64504" TransactionTypeID="1" QueueID="1"> <TicketMeta Name="Message">Test Case Desc</TicketMeta> <TicketMeta Name="Element ID 1">1111</TicketMeta> <TicketMeta Name="Element ID 2">2222</TicketMeta> <TicketMeta Name="Element ID 3">3333</TicketMeta> <TicketMeta Name="Element ID 3">3336</TicketMeta> <TicketMeta Name="Element ID 4">4444</TicketMeta> <TicketMeta Name="Element ID 5">5555</TicketMeta> <TicketMeta Name="Element ID 2">2222</TicketMeta> <TicketMeta Name="Element ID 3">3333</TicketMeta> <TicketMeta Name="Element ID 3">3336</TicketMeta> <TicketMeta Name="Element ID 4">4444</TicketMeta> <TicketMeta Name="Element ID 5">5555</TicketMeta> <TicketMeta Name="Event Window Start Date">11/26/2012</TicketMeta> <TicketMeta Name="Event Window Start Time">03:00</TicketMeta> <TicketMeta Name="Event Window End Date">11/26/2012</TicketMeta> <TicketMeta Name="Event Window End Time">18:00</TicketMeta> </Ticket></root>';SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN, TicketID, TransactionTypeID, QueueID, c2.value('.','varchar(64)') AS valFROM ( SELECT @x.query('for $a in /root/Ticket/TicketMeta return $a' ) , @x.value('(/root/Ticket/@TicketID)[1]','varchar(32)'), @x.value('(/root/Ticket/@TransactionTypeID)[1]','varchar(32)'), @x.value('(/root/Ticket/@QueueID)[1]','varchar(32)') )T1(c1,TicketID,TransactionTypeID,QueueID) CROSS APPLY c1.nodes('/TicketMeta') T2(c2);