Loading an XML file into a DB

  • Thread starter Thread starter Steven Blair
  • Start date Start date
S

Steven Blair

Hi,

Got a general query regarding XML and Databases.

To be honest, I really don't kno that much about XML.

Here is my problem:

I have a XMl file containing a number of transactions in a format
similar to this:

<MessagingHeader document-number="000256" advice-type="AUDACC"
subject-first-aosn="50000002" subject-last-aosn="50000007"
user-number="111111" stream-identifier="19990720001"
reprint-indicator="0" envelope-sequence-number="00000001"
report-generation-date="1999-12-20" user-name="THIS IS AN EXAMPLE OF A
TEST REPORT - USER NAME HERE" report-type="7003" />

I need a way of loading this into a DB table quickly and easily.

At the moment, I have a C# service which picks up files and has a class
for database activity.

Can anyone give me advice on how I should do this?

I have two ways it hink its possible, but correct me if i am wrong:

1. Read the file into a XmlReader and extratc each filed, building up a
SQL statment and add the transaction.

2. Somehow map the XML to a DataSet that magically imports the data from
a file.

I know some of this is a little vague, but anyone who can point me in
the right direction would be doing me a big favour :)

Regards,

Steven
 
Steven,

If you want quick and you use Sql Server you can create a stored procedure
using the OpenXML statement and pass the XML to the proc for the parsing. I
have written a sample for you that is posted below. If you need to do any
manipulation of the data I would strongly suggest that you stick with the
great XML namespace that .net provides.

I hope this helps.

------------------------------

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<MessagingHeader document-number="000256" advice-type="AUDACC"
subject-first-aosn="50000002" subject-last-aosn="50000007"
user-number="111111" stream-identifier="19990720001"
reprint-indicator="0" envelope-sequence-number="00000001"
report-generation-date="1999-12-20" user-name="THIS IS AN EXAMPLE OF A
TEST REPORT - USER NAME HERE" report-type="7003" />'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO MessagingHeader
SELECT *
FROM OPENXML (@idoc, '/MessagingHeader',1)
WITH ( docnum varchar(10) '@document-number',
advicetype varchar(10) '@advice-type',
subjectfirstaosn varchar(20) '@subject-first-aosn',
subjectlastaosn varchar(20) '@subject-last-aosn',
usernumber varchar(20) '@user-number',
streamidentifier varchar(20) '@stream-identifier',
reprintindicator varchar(20) '@reprint-indicator',
envelopesequencenumber varchar(20) '@envelope-sequence-number',
reportgenerationdate datetime '@report-generation-date',
username varchar(100) '@user-name',
reporttype int '@report-type')

EXEC sp_xml_removedocument @idoc
 
Back
Top