SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 reading nodes from xml
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimoomba
Yak Posting Veteran

India
88 Posts

Posted - 12/05/2012 :  08:57:11  Show Profile  Reply with Quote
Hi All,

Iam having the xml as shown below :

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

And i want the output to display as follows using sql query for reading the nodes. and also the xml can contain any number of ticketmeta tags:

col1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15
64504 1 1 Test Case Desc 1111 2222 3333 3336 4444 11/26/2012 03:00 11/26/2012 18:00

kindly help me on the same

rams

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/05/2012 :  10:03:02  Show Profile  Reply with Quote
quote:
also the xml can contain any number of ticketmeta tags
That makes it harder - you will need to shred the XML and then use dynamic pivot. Is that really the case, or can some boundaries/parameters be put on the number of elements?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/05/2012 :  10:13:28  Show Profile  Reply with Quote
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 described
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);
Go to Top of Page

jimoomba
Yak Posting Veteran

India
88 Posts

Posted - 12/06/2012 :  03:30:16  Show Profile  Reply with Quote
Thanks a lot sunita,
it really worked like a charm and this is what iam exactly looking for.
Once again thanks a lot

rams
Go to Top of Page

subashseo
Starting Member

Barbados
4 Posts

Posted - 01/19/2013 :  07:17:37  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000