Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Non unique id xml node -flatten and import in to sql Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 5:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 1:32 PM
Points: 3, Visits: 26
Hi i need to import the xml in to sql. there is no item or value in invoice node its under invoice header node. please help me to import this in to sql
i want to store each invoce and its item details in sql tables

<invoicetransmission>
<invoicetransmissionheader>
<invoiveid>333</invoiceid>
</invoicetransmissionheader>
<invoice>
<invoiceheader>
<invoicenumber>33</invoicenumber>
<items>
<itemid>44</itemid>
</items>
<invoice>
<invoice>
<invoiceheader>
<invoicenumber>323</invoicenumber>
<items>
<itemid>424</itemid>
</items>
<invoice>
</invoicetransmission>
Post #1376931
Posted Thursday, October 25, 2012 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 2,701, Visits: 1,694
To shred the xml to flat tables, you will need to use some of the xml data type methods and maybe a little bit of XQuery. the nodes() method will help you shred the xml into a relational format. the value() method will also help you extract data as well.

Here are some links to get you started.

http://msdn.microsoft.com/en-us/library/ms188282.aspx
http://msdn.microsoft.com/en-us/library/ms178030.aspx

http://msdn.microsoft.com/en-us/library/ms189075.aspx

However, have you posted live data in your example? it does look surprisingly realistic? and the addresses in the xml seem to be genuine for customers in the xml.. I hope I'm wrong and that the data is just test data and I wish that I had such good quality test data to work with
Post #1376955
Posted Thursday, October 25, 2012 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: Administrators
Last Login: Today @ 4:12 AM
Points: 16, Visits: 2,476
Have you obfuscated your data in your post so you're not revealing anything you shouldn't be? This looks like an actual invoice.
Post #1376959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse