Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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  
0
R8VI
Asked:
R8VI
  • 5
  • 3
1 Solution
 
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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
Anthony PerkinsCommented:
>>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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now