Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Question Unanswered: How to parse XML inside stored procedure

    Hello,
    I have following problem:

    have table 'xml_documents' with columns 'id' AS int and 'xml' AS text

    What I want to do:
    I would like to write stored procedure with one parameter 'id'. This procedure should parse 'xml' and return some kind of result.
    example:

    declare @idoc int
    declare @doc varchar(8000)

    set @doc = (SELECT CAST(xml AS varchar(8000)) FROM xml_documents WHERE id = 1335)

    exec sp_xml_preparedocument @idoc OUTPUT, @doc
    SELECT text
    FROM OPENXML (@idoc, '//COMPONENT')
    WHERE nodetype = 3
    EXEC sp_xml_removedocument @idoc


    NOW THE PROBLEM:
    Our xml documents stored in table 'xml_documents' are much bigger than 8000 bytes. And there is no way how to put 'xml' column value directly as a parameter of sp_xml_preparedocument procedure. Ofcourse I can not create local variable using type 'text'.

    QUESTION:
    Is there any way how to read xml stored in table columnt as type text and call procedure sp_xml_preparedocument using this value?

    Thanks,
    Zdenek

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138

  3. #3
    Join Date
    Feb 2003
    Posts
    2
    Originally posted by Memnoch1207
    maybe this will help
    http://www.sqlxml.org/topics.aspx?topic=1
    Thanks. I know about splitting into multiple varchars.

    But this example is not suitable in our situation. XML text can be large and 1000 x varchar(8000) variables is not good solution. :-(

    Is there any way how to do this without splitting text into multiple varchar variables?

    Thanks,
    Zdenek

Posting Permissions

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