Capturing XML

Ok, this is a broad question. Our system is going to receive xml files from vendors that contain background check results for individuals. We want to capture the xml results in the db for historical purposes and to have a copy of what the vendor sent before we translated the results and updated our system. I was going to store the xml in a table with a text column but wasn't sure if there was a better way to do this. Anyone have any suggestions?

ThanksI think there have a couple of posts recently on this very subject. I think the general consensus is to store the XML in an NText or Text column. Individual contributors have cautioned that these column types require additional "attention" in the form of more frequently scheduled DBCC CHECKDB statements. That has not been my experience, but others have suggested it.

Certainly in your case, since you want to keep a copy of the data received prior to manipulating it, storing it "as-is" is probably a good idea. An alternative might be to store the filename (as a pointer) in the database while saving the XML to a file on either the database server or some other location.

Regards,

hmscott|||Well, you're going to need to use the data too, right?

DECLARE @.xml varchar(1000)

SET @.xml = '<?xml version="1.0" encoding="ISO-8859-1"?>
<InsertSessionData>
<Input>
<SessionID>6F9619FF-8B86-D011-B42D-0C04FC964FF</SessionID>
<Last_Accessed>20030101</Last_Accessed>
<State><somexml></somexml></State>
</Input>
</InsertSessionData>'

DECLARE @.idoc int

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml

SELECT * FROM OPENXML(@.idoc, '/InsertSessionData/Input', 2)
WITH(
SessionID varchar(8000),
Last_Accessed Datetime,
State Text
)|||Brett,

Your point is well taken. However, there are other ways of getting at the data in the documents. We use a VBScript and the MS DOM to strip out the necessary data and store it in the relational tables. We don't keep a copy of the XML docs (no requirement to do so), but if I did, I could simply add a line to insert the text into an archive table for historical purposes.

But I don't use OPENXML to parse the XML itself.

Regards,

hmscott|||Thanks for the ideas. We're going to extract the data we need through java code. The table is strictly for archiving. Kind of an insurance policy if we have problems with our interface application.