DECLARE @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 val
FROM
(
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);
OUTPUT:
RN TicketID TransactionTypeID QueueID val
1 64504 1 1 Test Case Desc
2 64504 1 1 1111
3 64504 1 1 2222
4 64504 1 1 3333
5 64504 1 1 3336
6 64504 1 1 4444
7 64504 1 1 5555
8 64504 1 1 2222
9 64504 1 1 3333
10 64504 1 1 3336
11 64504 1 1 4444
12 64504 1 1 5555
13 64504 1 1 11/26/2012
14 64504 1 1 03:00
15 64504 1 1 11/26/2012
16 64504 1 1 18:00