Reading XML file and store it in SQL Server Db

Question

Reading XML file and store it in SQL Server Db

GAURAVKAUSHIK26

SSC Veteran

Points: 274
More actions
August 18, 2009 at 10:32 pm

#71753

Hi I have an XML file which I want to load in sql server database. Can you pls advise how to do it.
It has which contains multiple
and each section can have multiple
This is the XML sample record:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

to begin with a short advise: When posting xml data please use the relevant code tags (a list of IFCode Shortcuts is on the left side of the input box when you write a post). Otherwise it's displayed like the one in your post above...

Regarding the subject:

attached please find a proposal on how to shred your xml data.

For more details on how to deal with xml data I recommend to read Jacob Sebastians articles "XML Workshop ..." (search for the keywords on this site to get the list).

DECLARE @xml xml

SET @xml = '

'

SELECT

c.value('@RateChangeReferenceDate[1]','varchar(30)') AS RateChangeReferenceDate,

c2.value('@CorrelationId[1]','varchar(30)') AS CorrelationId,

c2.value('@Premium[1]','varchar(30)') AS Premium,

c2.value('@AnnualisedPremium[1]','varchar(30)') AS AnnualisedPremium,

c2.value('@Rate[1]','varchar(30)') AS Rate,

c2.value('@TerrorismLevy[1]','varchar(30)') AS TerrorismLevy,

c2.value('@FireServicesLevy[1]','varchar(30)') AS FireServicesLevy,

c2.value('@GST[1]','varchar(30)') AS GST,

c2.value('@StampDuty[1]','varchar(30)') AS StampDuty,

c2.value('@RatingMethod[1]','varchar(30)') AS RatingMethod

FROM @xml.nodes('RATEDPOLICY/SECTION') AS T(c)

CROSS APPLY T.c.nodes('COVER') AS T2(c2)

/*result set

RateChangeReferenceDateCorrelationIdPremiumAnnualisedPremiumRateTerrorismLevyFireServicesLevyGSTStampDutyRatingMethod

2009082552552000474.00474.000.0000.000.0047.4057.35Auto

20090825575520001.001.000.0000.000.000.100.12Auto

2009082549549900175.00175.000.0000.000.0017.5021.18Manual

2009082540059100125.00125.000.0000.000.0012.5015.13Auto

200908254005900080.0080.000.0000.000.008.009.68Auto

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mauve SSChampion Points: 11316 More actions · Answer 2

It depends upon what you wish to do with the XML. Just store it, "shred" it into a relational structure, etc.

In addition to the documentation in the Books Online (BOL), and Jacob's nice tutorials, here are links to a number of Microsoft Technical articles that describe how SQL Server 2005 supports XML:

XML Support in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx

XML Options in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345110(SQL.90).aspx

What's New in FOR XML in Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345137(SQL.90).aspx

XML Indexes in SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx

XML Best Practices for Microsoft SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms345115(SQL.90).aspx

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

gmsharma11 Valued Member Points: 58 More actions · Answer 3

Hi Guys,

Please help me out!! AsAp...

I have a unstructured XML files …. Means … each file can have different no of NODES…

So please let me know how I can upload this file if I do not know what fields are coming in this file.

I need query to upload unstructured XMl file in sql server2008

Thanks

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

gmsharma11 (1/5/2015)
Hi Guys,
Please help me out!! AsAp...
I have a unstructured XML files …. Means … each file can have different no of NODES…
So please let me know how I can upload this file if I do not know what fields are coming in this file.
I need query to upload unstructured XMl file in sql server2008
Thanks

Quick suggestion, post the question as a new thread under the XML topic and preferably some sample data with the expected results if applicable.

😎