load the xml int the DB after split the xml

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

load the xml int the DB after split the xml

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 21:54:34

HI All,

Iam having the xml as shown below :

<?xml version="1.0" encoding="UTF-8"?>
<MessageDelivery version="B000">
<ReturnMessage id="3654397910">
<AdC ocean="AORWGL">4815043</AdC>
<MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus>
<MessageData>700A200000160039C943C00000</MessageData>
<Flags les="0" app="0" read="1"/>
</ReturnMessage>
<ReturnMessage id="3655041356">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-26 01:43:20">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" read="1"/>
</ReturnMessage>
</MessageDelivery>

I want to split this xml into two and save it in the database table based on returnmessage id, kindly let me know the sql query plz..

rams

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 22:21:33

What do you mean split the xml into two? Did you mean store it as *2 records* in a table?

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 22:26:11

Hi vadivel,

Good Morning.first i want to split thee xml and save it as 2 recrds in a table.

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 23:05:58

Check out the various XQUERY samples here - http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

That should help you get started on this.

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 01:23:09

use query below


declare @x xml
set @x='<?xml version="1.0" encoding="UTF-8"?>
<MessageDelivery version="B000">
<ReturnMessage id="3654397910">
<AdC ocean="AORWGL">4815043</AdC>
<MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus>
<MessageData>700A200000160039C943C00000</MessageData>
<Flags les="0" app="0" read="1"/>
</ReturnMessage>
<ReturnMessage id="3655041356">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-26 01:43:20">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" read="1"/>
</ReturnMessage>
</MessageDelivery>'
declare @xmltable table
(id int identity(1,1),
xmldata xml)

insert into @xmltable
select f.c.query('.')
from @x.nodes('/MessageDelivery/ReturnMessage')f(c)

select * from @xmltable


output
-------------------------
id          xmldata
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           <ReturnMessage id="3654397910"><AdC ocean="AORWGL">4815043</AdC><MessageStatus code="100" time="2011-09-25 22:02:55">status ok</MessageStatus><MessageData>700A200000160039C943C00000</MessageData><Flags les="0" app="0" read="1" /></ReturnMessage>
2           <ReturnMessage id="3655041356"><AdC ocean="PACCGL">4815044</AdC><MessageStatus code="100" time="2011-09-26 01:43:20">status ok</MessageStatus><MessageData>700A20000018C0375494400000</MessageData><Flags les="0" app="0" read="1" /></ReturnMessage>

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 02:03:02

Thanks a log guys! That worked for me exactly

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 02:13:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 02:24:38

Hi Visakh,

Could you please help me on bitmask value in sql server. How do we declare them and how do we use them.I need it very badly.

Thanks,
Ram

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 04:33:31

quote:
Originally posted by jimoomba

Hi Visakh,

Could you please help me on bitmask value in sql server. How do we declare them and how do we use them.I need it very badly.

Thanks,
Ram

rams

see

http://consultingblogs.emc.com/jamesrowlandjones/archive/2008/07/04/using-a-bitmask-a-practical-example.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources