XML Reader ad store data in theDB

Hi,

I have an xml File with about 1200 records

I wanted to get the data from the XML file and store it into an SQL DB

Which option would be the quickest time wise

To have something in sql like this
http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

Or to make a C# reader read the data and put into the DB

please help

Thanks,

R8VI  
R8VIAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>by that do you mean something like <<
No.  Something like this (untested):
DECLARE @x Xml

SELECT  @x = CAST (x AS XML)
FROM    OPENROWSET(BULK 'C:\XMLFileProperties\2011-09-25_03-04-22.xml', SINGLE_BLOB) AS T (x)

INSERT  Properties(PropertyID,  ClientID, Address1)
SELECT  X.Properties.query('PropertyID').value('.', 'INT'),
        X.Properties.query('ClientID').value('.', 'INT'),
        X.Properties.query('Address1').value('.', 'VARCHAR(100)')
FROM    @x.nodes('Properties/Property') AS X (Properties) ;

Open in new window

0
 
Anthony PerkinsCommented:
It really depends on your environment.  Many large shops do now allow the use of OPENROWSET(), let alone access to files from SQL Server.  For that reason a .NET app is a more flexible and portable way of importing an Xml file.
0
 
Anthony PerkinsCommented:
But I should have added if you do go the C# way, make sure to pass the entire Xml document to SQL Server, rather than insert each node one at a time.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
R8VIAuthor Commented:
Hi acperkins,

Thanks for the advise however the XML file will be stored on my server and I need to insert the data from the XML file into my SQL server so wont need access from another server.

I did a test app and inserted the records through the link I provided above and took 30 mins which I thought was way too long

So still not sure which method would be the best

Thanks,

R8VI
0
 
Anthony PerkinsCommented:
>>took 30 mins which I thought was way too long <<
I agree.  How big is the Xml file?  Can you post a snippet obfuscated if necessary, as well as the excat code used?

0
 
R8VIAuthor Commented:
Yep sure,

The file in size is 1.00MB

XML snippet

<?xml version="1.0" encoding="UTF-8"?>
<Properties>
  <Property>
    <PropertyID>1</PropertyID>
    <ClientID>6</ClientID>
    <Address1>testAdd1</Address1>
    <Address2>testAdd2</Address2>
    <Address3>testAdd3</Address3>
    <Town>testTown</Town>
    <Postcode>testPostcode</Postcode>
    <Bedrooms>testBedrooms</Bedrooms>
    <Price>testPrice</Price>
    <Summary>testSummary</Summary>
    <Description>testDescription</Description>
    <PropertyType>testPropertyType</PropertyType>
    <Status>testStatus</Status>
    <Garage>testGarage</Garage>
    <ForSale>testForSale</ForSale>
  </Property>
</Properties>

SQL snippet:

I just wanted to add the first 3 bit of code as test for now which took 30mins anyways
INSERT INTO Properties (PropertyID, ClientID, Address1)
SELECT X.Properties.query('PropertyID').value('.', 'INT'),
            X.Properties.query('ClientID').value('.', 'INT'),
            X.Properties.query('Address1').value('.', 'VARCHAR(100)')
FROM (
            SELECT CAST (x as XML)
            FROM OPENROWSET (
            BULK 'C:\XMLFileProperties\2011-09-25_03-04-22.xml',
            SINGLE_BLOB) AS T(x)
       ) AS T(x)
CROSS APPLY x.nodes('Properties/Property') AS X(Properties);

Thanks for all your help so far

Thanks,

R8VI
0
 
Anthony PerkinsCommented:
Have you tried importing the file into an Xml variable using OPENROWSET() and then doing the INSERT?
0
 
R8VIAuthor Commented:
0
 
käµfm³d 👽Commented:
Are you trying to "shred" the XML document into separate columns (and/or tables), or are you trying to push the document into an xml-typed column?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.