Solved

XML Reader ad store data in theDB

Posted on 2011-09-25
9
346 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How we can add Dten600.dll in GAC in c#. 18 55
EF5: Update Model from Database not working 3 83
EF5 How do I stop pre-compiled views? 8 54
Pdf not loading 2 40
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

739 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