Solved

XML Reader ad store data in theDB

Posted on 2011-09-25
9
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 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