quote:

---

also the xml can contain any number of ticketmeta tags

---

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