Using OpenXML to import into SQL 2000

  • I'm using OpenXML to import an XML document into SQL server table, but the problem I'm running into is my XML document is larger than a varchar or nvarchar can hold.

    Is there a "Character" datatype that can hold more than 8000 bytes???

    here's my code

     
    
    SET NOCOUNT ON


    -- Let's now first read the XML file into a temporary table
    -- Create temporary table first
    CREATE TABLE #tmpFileLines (rowID int IDENTITY, lineData nvarchar(255))

    -- Insert lines from files into temp table (using xp_cmdshell)
    INSERT #tmpFileLines EXEC master.dbo.xp_cmdshell 'TYPE \\jepeters\1.xml'

    DECLARE @strXMLText nVARCHAR(4000)

    -- Reading the XML data from the table into a string variable
    -- This string variable is used with OPENXML
    SELECT @strXMLText =
    CASE rowID WHEN 1 THEN
    ISNULL(RTRIM(lineData), '')
    ELSE
    @strXMLText + ISNULL(RTRIM(lineData), '')
    END
    FROM #tmpFileLines ORDER BY rowID ASC


    PRINT '-------------------------------'
    PRINT 'Bytes read from the file:'
    PRINT DATALENGTH(@strXMLText)
    PRINT @strXMLText
    PRINT '-------------------------------'

    DROP TABLE #tmpFileLines

    --Preparing for calling OPENXML
    DECLARE @hDoc int
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText

    --Inserting using OPENXML

    INSERT INTO PoHeader
    SELECT *
    FROM OPENXML(@hDOC, '/aXML/Header', 2)
    WITH
    (
    Title nvarchar(255),
    Publisher nvarchar(255),
    DateOfPurchase datetime
    )

    EXEC sp_xml_removedocument @hdoc
    GO

    SELECT * FROM PoHeader
    GO

    SET NOCOUNT OFF

  • have you tries text columns?.

  • yup

    the message I get is

    The text, ntext, and image data types are invalid for local variables.

  • You'll have to load the file outside of the proc and pass it in using a text variable.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Sounds like it should be simple.

    Do you have an example of how this is done??

  • Not handy. The fun part is because you can't use text as a local var type, there is no way to load it that I know of except outside TSQL. You'll have to open the doc with DTS or whatever, then call the proc, passing the XML doc in.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Maybe I can load the file into a VBscript variable then use isql or osql to execute the stored procedure, passing the variable as the parameter?

    Sounds like it should work...in theory

  • create procedure spTest

    @txt text

    as

    select @txt

    go

    Try this for the variable of the OpenXML part.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • Well,

    I got the thing working in the Query Analyzer, but I'm getting an error in my Vbscript whne using the Wscript.Shell Object. I hope the Run command doesn't have a length restriction on it, but it sure does look that way. My script works fine when I invoke isql from the command line without the parameter...but when I add it, it gives me an error...

    Still debugging

  • I do a lot of imports this way working with xml files which are several mb...

    I have a VBS file that opens the xml document into a local variable...

    --------------------- SNIP ---------------------

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set theFile = objFSO.OpenTextFile(xmlFileName,1)

    theXML = theFile.ReadAll

    theFile.close

    --------------------- SNIP ---------------------

    And then pass it through to a SP as a TEXT type...

    --------------------- SNIP ---------------------

    adoCmd.CommandText = "xml_import"

    adoCmd.Parameters.Refresh

    adoCmd.Parameters.Item(1).Value = theXML

    adoCmd.Parameters.Item(2).Value = "BASICS"

    Set adoRS = adoCmd.Execute()

    --------------------- SNIP ---------------------

    The SP is basically...

    --------------------- SNIP ---------------------

    CREATE Procedure xml_import

    (

    @xmlDocntext,

    @whichSectionvarchar(50)

    )

    As

    set nocount on

    declare

    @ReturnCodeint,

    @iDocint

    execute sp_xml_preparedocument @iDoc OUTPUT, @XMLDoc

    if @whichSection = 'BASICS'

    begin

    -- do my processig here with OPENXML based on @iDoc

    end

    -- other code here....

    -- always close th document...

    execute sp_xml_removedocument @iDoc

    --------------------- SNIP ---------------------

  • what is the problem in passing the entire xml to a sp as an input parameter of datatype text?

  • That is what i have suggested, the above code passes the XML as ntext (Unicode Text).

    Dan

  • You might want to use Microsoft SQLXML to bulkload the XML file into your SQL table. I have been using it and it has worked fine.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply