Solved

XML Reader ad store data in theDB

Posted on 2011-09-25
9
342 Views
Last Modified: 2012-08-14
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
Comment
Question by:R8VI
  • 5
  • 3
9 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595299
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595302
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
 

Author Comment

by:R8VI
ID: 36595319
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595349
>>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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:R8VI
ID: 36595362
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36595421
Have you tried importing the file into an Xml variable using OPENROWSET() and then doing the INSERT?
0
 

Author Comment

by:R8VI
ID: 36595431
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36596232
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 36596312
>>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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now