split up the xml fileand load into the DB table

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

split up the xml fileand load into the DB table

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 10:29:17

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 provide me the code plz..

rams

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-09-30 : 14:42:22

Look at openrowset and charindex. that should give you what you want.
Pete

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-30 : 16:05:02

Alternatively, you can use SQL XQUERY functions - in your example, assuming the XML in your example is stored in a variable named @yourXMLVariable, you can do this:


SELECT
   c.query('data(./@id)') AS idCol,
   c.query('.') AS xmlCol
FROM
   @yourXMLVariable.nodes('//ReturnMessage') T(c)

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-01 : 08:56:35

Thanks a lot!! it worked for me exactly:)

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources