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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 getting XML data from table column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrekoj
Starting Member

2 Posts

Posted - 06/10/2013 :  23:55:16  Show Profile  Reply with Quote
Hi,
I have a table that stores XML data.
The examples found are mainly used on 1 set of XML data, since I'm breaking up the xml in the table column, I will have multiple set of xml data.
May I know how do I retrieve the data without using cursor ?


my data


MsgID        Data
---------------------------------------------------
1            <event>
                  <msg langID = "1">
                         <title> hello </title>
                         <main> body </title>
                  </msg>
                  <msg langID = "2">
                         <title> hola</title>
                         <main> body </title>
                  </msg>

2            <event>
                  <msg langID = "1">
                         <title> msg2head </title>
                         <main> msg2body </title>
                  </msg>
                  <msg langID = "2">
                         <title> msg2headinChinese </title>
                         <main> msg2bodyInChinese </title>
                  </msg>





The result i wanted


MsgID     LangueID      Title              Main
---------------------------------------------------
1         1             hello              body 
1         2             hola               body 
2         1             msg2head           msg2body  
2         2             msg2headinChinese  msg2bodyInChinese 



additional info
the number of langID per xml in the data is not fixed.

Thanks in advance :)

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 06/11/2013 :  01:14:23  Show Profile  Reply with Quote
your xml sample , i guess , is not correct
end tag does not match with start tag

1-<event> , does not close
2-<main> body </title> = <main> tag close with <title> ?

those are correct?

for test , I change to something like this :

<event>
                  <msg langID = "1">
                         <title> hello </title>
                         <main> body </main>
                  </msg>
                  <msg langID = "2">
                         <title> hola</title>
                         <main> body </main>
                  </msg>
				</event>


here is my solution


select 
	xmlTable.[MsgID] as [MsgID]
	,t.u.value('@langID[1]','nvarchar(30)') as [langID]
	,t.u.value('title[1]' ,'nvarchar(30)') as [title]
	,t.u.value('main[1]','nvarchar(30)') as [main]
from xmlTable 
	cross apply [xmlTable].Data.nodes('event/msg') as t(u)





Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

mrekoj
Starting Member

2 Posts

Posted - 06/11/2013 :  23:50:05  Show Profile  Reply with Quote
Sorry for the mistake in my xml.
was typing it in a rush before I left office yesterday.

you got what I meant.
Just tested your sql, it's working perfectly, thanks !!
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 06/12/2013 :  01:47:43  Show Profile  Reply with Quote
with welcome

Sabin

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
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