XML to SQL2005.net2.0 unattended

[hdjim69]

Hello, I have an xml document from a vendor I need to dump into a sql
table. The document is product information containing product
category, name, description, price, etc. We get the file in the wee
hours of the morning. I'm trying to figure out the best way to
develop this. Should I write an app in c# using the System.Xml
Namespace and parse the xml document and looping and dumping into a
table? Slow? Or is there a better way to handle this?

Also, I'm kinda new to c# so if anyone knows any c# class examples
that parse an xml document, I'd appreciate the links or sample code to
follow.

TIA
J

 

[Alberto Poblacion]

Hello, I have an xml document from a vendor I need to dump into a sql
table. The document is product information containing product
category, name, description, price, etc. We get the file in the wee
hours of the morning. I'm trying to figure out the best way to
develop this. Should I write an app in c# using the System.Xml
Namespace and parse the xml document and looping and dumping into a
table? Slow? Or is there a better way to handle this?

You don't really need to do it in C#. SQL Server 2005 is able to parse
XML all by itself, by means of the OPENXML function:

CREATE PROCEDURE ProcesMyXml
@doc xml
AS

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

INSERT INTO TheTable SELECT *
FROM OPENXML (@hdoc, '/Customer/Order/OrderDetail', 1)
WITH (CustomerID int '../../@CustomerID',
OrderID int '../@SalesOrderID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Quantity int)

EXEC sp_xml_removedocument @hdoc

GO

 

[hdjim69]

You don't really need to do it in C#. SQL Server 2005 is able to parse

XML all by itself, by means of the OPENXML function:

WOW! This is new to me. is sp_xml_preparedocument a native sql sp?
I need to look into the OPENXML function. What is a good source of
info on this? a good book?

And after the sp is created, you just set this up as a job? wow....if
this works it will save a lot of coding...!!

j

 

[Alberto Poblacion]

WOW! This is new to me. is sp_xml_preparedocument a native sql sp?
I need to look into the OPENXML function. What is a good source of
info on this? a good book?

And after the sp is created, you just set this up as a job? wow....if
this works it will save a lot of coding...!!

Yes, the sp_xml_preparedocument is native.
OPENXML is reasonably well documented in Sql Server's Books On Line.
Enter "OPENXML function" in the Index, and follow the link at the bottom of
the article for "Querying XML Using OPENXML".
Also on MSDN:
http://msdn2.microsoft.com/en-us/library/ms186918.aspx
http://msdn2.microsoft.com/en-us/library/ms175160.aspx

 

[hdjim69]

Yes, the sp_xml_preparedocument is native.

OPENXML is reasonably well documented in Sql Server's Books On Line.
Enter "OPENXML function" in the Index, and follow the link at the bottom of
the article for "Querying XML Using OPENXML".

I did some research on this and using OPENXML is not recommended on
anything but small xml files due to the fact OPENXML is very memory
intensive which could bring the system down. Not good.

"OPENXML is very memory intensive. The pointer returned by the system
stored procedure sp_xml_preparedocument is a memory pointer to a COM
XML document object model. So, you must be careful not to load large
XML documents into memory with OPENXML because it may overload your
server's memory."

here is the link to this citation: http://articles.techrepublic.com.com/5100-9592-6138776.html

 

[Alberto Poblacion]

I did some research on this and using OPENXML is not recommended on
anything but small xml files due to the fact OPENXML is very memory
intensive which could bring the system down. Not good.

Yes, the sp_xml_preparedocument loads the document into memory and
returns a handle to it. If the file is very large, you will want to process
it using C# code. And you want to use an XmlReader, which lets you process
the file sequentially. If you try to use an XmlDocument (which would
probably be simpler), you would encounter the same problem, because the
XmlDocument also loads the entire document into memory.