Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002

    Unhappy Unanswered: XML in a text field??

    I want to save an XML file into a text field. That is the easy stuff I think, but I will need to search into this XML file.

    I ' m trying to use it in an stored procedured, but I donīt know how to pass the text field as a parameter. And after that It seems not to be possible to use the OPENXML with a text parameter, I need to use the sp_xml_preparedocument but I donīt succes to make it works.

    Does anyone know how to do this? I have found something that break the text field into several varchar of 8000 characters, but this implicates to know the exact length of the text file.

    Thanks in advance...

  2. #2
    Join Date
    Jul 2002
    Is the core problem passing the text as a parameter to the SP?

  3. #3
    Join Date
    Nov 2002

    That could help...

    That could help, if i could be able to past the content of the column as a parameter i could do something.

    Anyway if you any other way to search into a text field that content an XML file it would be welcome.


    I have an XML file as :

    <m n='1' rg='23' max='45' min='5' />
    <m n='2' rg='11' max='42' min='4' />

    in a text column, and in some way I have to know the value of the attribute "rg" when n="2"

    Thanks ...

  4. #4
    Join Date
    Nov 2002
    I have this procedure, but i don't know how to pass the content of a column of type text:

    table a(a1 varchar(2),a2 text)

    i want to save an xml file in a2 and then call sp_mio as:

    execute sp_mio @entrada=select a2 from a where .....


    CREATE PROCEDURE sp_mio @entrada text AS
    declare @idoc int

    exec sp_xml_preparedocument @idoc OUTPUT, @entrada

    SELECT *
    FROM OPENXML (@idoc, '/m',2)
    WITH (v1 int './@v1',
    v2 int './@v2')

  5. #5
    Join Date
    Jul 2002

    Re: That could help...

    Don't know if I'm on the proper road, but I tried creating a procedure like this:

    create procedure xml_play @doc text as

    DECLARE @idoc int

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    -- Execute a SELECT statement that uses the OPENXML rowset provider.
    FROM OPENXML (@idoc, '/ROOT/m',1)
    WITH (n varchar(10),
    rg varchar(10),
    max varchar(10),
    min varchar(10))
    WHERE n = '2'


    Then, to run it I did:

    exec xml_play '
    <m n="1" rg="23" max="45" min="5" />
    <m n="2" rg="11" max="42" min="4" />

    and it replied "11"

    Notice: This was just a quick test, maybe the numbers shouldn't be declared as varchars in reality.

  6. #6
    Join Date
    Nov 2002
    That's it!!

    Now imagine that you want to call the procedure with the result of a select statement (which returns the content of a text column) as parameter...

    execute xml_play (and in this place i want to pass the result of a select like : select column2 from tab1 where i have my xml document...)

  7. #7
    Join Date
    Jul 2002
    Seems like we get stuck on the fact that you can't declare a local variable as Text.
    If there is a way to directly connect the result of a Select into the input parameter of an SP, something like

    exec xml_play '@doc = select top 1 xmlstring from xmlplay'

    , this would solve the problem.

    Could you select the text string from the table and convert the result into a varchar (assuming that it's max 8000 chars)?

  8. #8
    Join Date
    Nov 2002
    I have tried a lot of ways to pass the text.. but if i use something like what you have said, it interprets that the value of the text is just the select statement and said that is not a valid XML head...

    I have found sthing that could help to solve...:

    DECLARE @xml_he int
    DECLARE @xml_0 varchar( 8000 ), @xml_1 varchar( 8000 ),
    @xml_2 varchar( 8000 ), @xml_3 varchar( 8000 )

    -- Strip the text value into several varchar strings:
    SELECT @xml_0 = SUBSTRING( xml_text, ( 0*8000 ) + 1, 8000 ),
    @xml_1 = SUBSTRING( xml_text, ( 1*8000 ) + 1, 8000 ),
    @xml_2 = SUBSTRING( xml_text, ( 2*8000 ) + 1, 8000 ),
    @xml_3 = SUBSTRING( xml_text, ( 3*8000 ) + 1, 8000 )
    FROM tbl

    -- Use dynamic SQL to build the prepare doc call:
    'DECLARE @Handle int
    EXEC sp_xml_preparedocument @Handle OUT, ''' +
    @xml_0 + @xml_1 + @xml_2 + @xml_3 + '''
    OPEN he_cur
    FETCH he_cur INTO @xml_he
    DEALLOCATE he_cur

    IF @xml_he IS NULL
    RAISERROR( 'Invalid Handle!', 16, 1 )

    -- Now use OPENXML with the handle in @xml_he

    EXEC sp_xml_removedocument @xml_he

    But this has a big disadvantage.. that It's neccesay to know the length of the text field.. and It canīt be bigger than the size you have supposed to be the biggest..

    I'm trying something with cursos.. maybe that could help...


Posting Permissions

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