Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MD
    Posts
    2

    Unanswered: sp_xml_preparedocument

    Trying to use this system stored procedure to parse an XML document stored as NTEXT in a table. Is there any way to do this without the limitation of using an NVARCHAR (or similar) non-large object? The NTEXT field frequently contains over 20,000 characters. Anyone have any hints?
    I tried defining a function that would do this, but only extended stored procedures are allowed to be executed. Local variables in a stored procedure are limited to non-large objects.
    Too bad this wasn't made into a system function so I could just do a "Select IntHandle = sp_xml_preparedocument(XMLColumnName) from TableName".

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

    Re: sp_xml_preparedocument

    It sure is quiet here. I am having an identical issue. I would LOVE to hear an answer.

    Regards,

    Hugh Scott

    Originally posted by RonClarke
    Trying to use this system stored procedure to parse an XML document stored as NTEXT in a table. Is there any way to do this without the limitation of using an NVARCHAR (or similar) non-large object? The NTEXT field frequently contains over 20,000 characters. Anyone have any hints?
    I tried defining a function that would do this, but only extended stored procedures are allowed to be executed. Local variables in a stored procedure are limited to non-large objects.
    Too bad this wasn't made into a system function so I could just do a "Select IntHandle = sp_xml_preparedocument(XMLColumnName) from TableName".

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    sp_xml_preparedocument IS extended stored procedure
    I repair - IT IS NOT!
    Last edited by ispaleny; 01-09-03 at 11:47.

  4. #4
    Join Date
    Jan 2003
    Posts
    6

    Re: sp_xml_preparedocument

    I just ran into this problem the other day and the only way I was able to work around it was to have an external app, or dll query the NTEXT table and then join all the rows together into one long string and then pass that string into my SP that was running the sp_xml_preparedocument, since you can define a variable as TEXT or NTEXT for a stored procedure.

    Hope this helps some!

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: sp_xml_preparedocument

    /*
    This sp_xml_preparedocument is internal server SP (pseudo SP). In EM it is in Extended store procedures section
    (That's why I posted some bullshit)
    */

    --Try this 2 SP code:

    drop proc dbo.usp_ParseXML
    GO
    create procedure dbo.usp_ParseXML(@ntext ntext)
    as begin
    declare @Handle int
    declare @RetVal int
    declare @Result int
    exec @RetVal=master.dbo.sp_xml_preparedocument @Handle OUTPUT,@ntext
    if @RetVal=0 begin
    exec @RetVal=master.dbo.sp_xml_removedocument @Handle
    set @Result=1
    end else begin
    set @Result=0
    end
    return @Result
    end
    GO
    drop proc dbo.usp_ParseXML_MyTable
    GO
    --Using identity
    create procedure dbo.usp_ParseXML_MyTable
    as begin
    set nocount on
    declare @c cursor
    declare @Id int
    declare @t table ("Id" int)
    declare @RetVal int
    set textsize 2147483647
    set @c=cursor read_only fast_forward for
    select IDENTITYCOL from MyTable
    open @c
    fetch next from @c into @Id
    while @@fetch_status=0 begin
    exec @RetVal=dbo.usp_ParseXML (select XMLColumnName from MyTable where IDENTITYCOL=@Id)
    if @RetVal=0 insert @t("Id") select @id
    fetch next from @c into @Id
    end
    close @c
    deallocate @c
    set nocount off
    select BadId="Id" from @t
    end
    GO

    exec dbo.usp_ParseXML_MyTable

    --Not tested, modify for your PK and table

  6. #6
    Join Date
    Jan 2003
    Location
    Columbia, MD
    Posts
    2
    The syntax "exec @RetVal=dbo.usp_ParseXML (select XMLColumnName from MyTable where IDENTITYCOL=@Id)" is not supported. I tried it anyway just to be sure, but it seems that parameters for the stored procedure must be either variables or contstants, but not a "select". So far Octavian's suggestion of an external process seems to be the only thing that works, but if I'm going to the trouble of getting the XML out of the database, why pass it back in just to parse it when I can parse it externally? Still open to other ideas or suggestions.

    Thanks!
    Ron

  7. #7
    Join Date
    Jan 2003
    Posts
    6
    I know you're opposed to running it thru a DLL, but with a DLL at least you can execute it from a DTS package running on SQL which you can either schedule or execute from a TSQL script of some sort. Then you have the DTS package call whatver SP you want to use with the XML data

    I hope this makes some sense.. my brain is fried today..

  8. #8
    Join Date
    Mar 2004
    Location
    UK
    Posts
    1
    I don't suppose anyone has come up with a clever way around this problem?

Posting Permissions

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