Retrieve data from xml file and insert into database table

Question

I am using c# .net 1.1 and SQL Server 2000....

I want to retrive data from an xml file and store that data into the database table.

XML file:

<information>
  <data>
    <id="1"></id>
    <name>peter</name>
    <age>25</age>
  </data>
</information>

My databse table is

id int,
name varchar(100),
age int

Answer 1

First off - your XML is invalid:

<data>
    <id="1"></id>

This is not a valid XML construct - what should it be??

An "ID" attribute on the data node? <data id="1">

An "ID" element with the value inside? <data><id>1</id>

There's a ton of ways you can do this - totally depends on your situation:

• create a XML schema so you can deserialize this XML into a .NET object - works best if you have tons of those files to import

• use Linq-to-XML if you're on .NET 3.5 and up

• use traditional XML document processing

If you use traditional XML document processing, this works best if your files are relatively small (since it loads the entire file into memory). In that case, you could do something like:

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("yourfilename.xml");

XmlNodeList dataNodes = xmlDoc.SelectNodes("/information/data");

foreach(XmlNode node in dataNodes)
{
    int id = Convert.ToInt32(node.SelectSingleNode("id").InnerText);
    string name = node.SelectSingleNode("name").InnerText;
    int age = Convert.ToInt32(node.SelectSingleNode("age").InnerText);

    // insert into database, e.g. using SqlCommand or whatever
}

As for inserting into a database - you can use a stored procedure, or a SQL statement in a SqlCommand - totally up to you. Once you have the three bits of information (id, name, age), you can do with those whatever you like.

Word of warning also: this includes no error checking whatsoever! If a <data> node should be incomplete or a sub node has a wrong name, this will crash - you need to provide some error checks, too! (left out for simplicity)

Marc

Answer 2

For when the XML file is visible to the SQL Server service, this works using plain old T-SQL, just tweak it to insert into your table.

DECLARE @DOC INT
DECLARE @XML VARCHAR(MAX) 

-- Dump entire file into variable
SET @XML =
( 
    select BulkColumn from openrowset 
        (BULK N'<your filename>'
            , single_clob) as RuleFile
)

-- Prep doc
EXECUTE sp_xml_preparedocument @DOC OUTPUT, @XML

-- Return data
SELECT * 
    FROM OpenXML(@DOC, '<your xpath>', 2) 
    WITH <your table>

-- Clean up
EXECUTE sp_xml_removedocument @DOC