Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Question Unanswered: Storing an XML string in a DB whilst using OPENXML

    Hi all,

    I've got a small problem using openxml.

    Basically I am passing XML as the input parameters for all of my strored procedures and using openxml to parse the input XML and insert the data onto the database etc.

    I have a problem in that I want to store an entire XML document in a particular database column. When I pass the XML within an xml document the openxml paser does not recognise it. Using an example should better describe the problem:

    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 UniqueIdentifier,
    Last_Accessed Datetime,
    State Text
    )

    This prodcues the output:
    SessionID=6F9619FF-8B86-D011-B42D-00C04FC964FF
    Last_Accessed=2003-01-01 00:00:00.000
    State=

    I want it to produce this:
    SessionID=6F9619FF-8B86-D011-B42D-00C04FC964FF
    Last_Accessed=2003-01-01 00:00:00.000
    State=<somexml></somexml>

    Does anybody have any ideas how I could do this? In fact is it possible?

    Many Thanks

    Steve

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, I've never worked with this

    Second, it looks way cool...

    Third, I don't think you can definea uniqueidentifier as a datatype for this...If you're getting it from somewhere, why not make it just varchar?

    Code:
    SELECT * FROM OPENXML(@idoc, '/InsertSessionData/Input', 2)
    WITH(
    SessionID varchar(8000),
    Last_Accessed Datetime,
    State Text
    )
    That works...
    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.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And how often do you get to see whilst used in a sentence?

    http://www.hyperdictionary.com/dictionary/whilst
    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.

Posting Permissions

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