Results 1 to 5 of 5

Thread: Capturing XML

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: 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?

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Capturing XML

    I 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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, you're going to need to use the data too, right?

    Code:
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    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

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •