Importing XML Data From XML File To SQL Database

Question

I'm new to C# and have been working on learning database's. Currently I'm trying to import data from a XML document that has items stored as follows:

   <CityData>
        <City>Ada</City>
        <County>Olmsted</County>
        <AreaCode>507</AreaCode>
        <Founded>1900</Founded>
        <CityWebSite>www.adacity.com</CityWebSite>
        <Population>1200</Population>
        <Zipcode>56996</Zipcode>
        <ZipcodeMax>57656</ZipcodeMax>
    </CityData>

I'm trying to save each data collection to a different row in a SQL Express database. I've been able to store other information in the database, I just don't know how to do this with a XML document. The database is stored locally on my machine along with the C# program I'm writing. I have a new table with columns named and ordered like the XML documents schema. The tutorials I'm finding online are for .asp programs. Has anyone ever done this? This is a C# Forms Program.

Which version of .net are you using? I ask because LINQ can make this pretty easy. — ptfaulkner
– ptfaulkner, Commented Mar 12, 2012 at 18:37
Please don't add "C#" to the ends of your titles. That's what the tags are for. — John Saunders
– John Saunders, Commented Mar 12, 2012 at 18:38
I'm using Visual C# Studio 2008 with SQL Server Express. I'll look up LINQ now, thanks. — user1143831
– user1143831, Commented Mar 12, 2012 at 18:41

Indi · Accepted Answer · 2012-03-12 18:44:46Z

2

This will do your job. please make sure to replace filePath\fileName.xml with full path to the xml and change /dataroot/CityData (at the bottom) as per your xml. Also sql should be able to access the file.

Declare @xml XML

Select  @xml  = 
CONVERT(XML,bulkcolumn,2) FROM OPENROWSET(BULK 'filePath\fileName.xml',SINGLE_BLOB) AS X

SET ARITHABORT ON

Insert into [YourTableName] 
        (
            City,County,AreaCode,Founded,CityWebSite,[Population],Zipcode,ZipcodeMax
        )

    Select 
        P.value('City[1]','VARCHAR(100)') AS City,
        P.value('County[1]','VARCHAR(100)') AS County,
        P.value('AreaCode[1]','VARCHAR(100)') AS AreaCode,
        P.value('Founded[1]','VARCHAR(100)') AS Founded,
        P.value('CityWebSite[1]','VARCHAR(100)') AS CityWebSite,
        P.value('Population[1]','VARCHAR(100)') AS Population,
        P.value('Zipcode[1]','VARCHAR(100)') AS Zipcode,
        P.value('ZipcodeMax[1]','VARCHAR(100)') AS ZipcodeMax,

    From @xml.nodes('/dataroot/CityData') PropertyFeed(P)

answered Mar 12, 2012 at 18:44

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

CFL_Jeff Over a year ago

Won't this only work if the XML file only has one CityData element?

user1143831 Over a year ago

Maybe my inexperience is holding me back but I don't have access to "Declare", I can use Select, I don't think I'm missing any using statements at the top. Is there something I'm suppose to do to get Declare @xml XML?

Indi Over a year ago

@CFL_Jeff I am assuming that CityData as a child node of dataroot.

Indi Over a year ago

This is a query and should be running on sql server query window or you could convert this to a stored procedure.

CFL_Jeff Over a year ago

@Indikaf What if there are multiple CityData elements in the file?

|

Community · Accepted Answer · 2017-05-23 11:58:43Z

1

First, you deserialize the XML into C# objects.

Then, you serialize the data from objects into the database.

Lucky you, C# makes XML Deserialization a snap.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Mar 12, 2012 at 18:44

CFL_Jeff

2,7596 gold badges34 silver badges53 bronze badges

Collectives™ on Stack Overflow

Importing XML Data From XML File To SQL Database

2 Answers 2

13 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related