Post reply

help in open XML(how to overcome file size limitation)

  • June 28, 2006 at 1:59 am

    #97187

    i have an xml file say abc.xml with schema defined in the file only. Problem is how to overcome file size limitation

    the code below will work best for small documents (< 8000 bytes).
    how to overcome this .
    I came to know that the workaround is available in the book The Guru's Guide to SQL Server Stored Procedures, XML, and HTML (chapter 15, page 449). But couldn't access this book.

    Explained the procedure i followed below. U need to see the page source of html page to get the proper format of xml file
    /*****************************************************************/
    Below is the content in the file

    ]>

    055551

    MY

    99999

    Sachin Prreseller1

    622222(949)240420222

    2.2.2.2

    LA

    555556

    11111111PT454654546123456

    06/13/06

    222222222PT898898123456

    06/13/06

    333333333PTM12345123456

    06/13/06

    PO BOX 123-123

    CA

    95458

    11111111PTM412345123456

    06/13/06

    055553

    CA

    92618

    Joli Prreseller3

    45465558444614920

    127.0.0.1

    NY

    89014

    4141414141PTM4012345123456

    06/13/06

    /******************************************************************/

    This is how i handled it

    Made tables in the database

    /*************************************************/

    create table reseller (qd varchar(50),company_name varchar(50),[user_name] varchar(50),customer_company_name varchar(50))

    create table customer (company_name varchar(50),serial varchar(50))

    create table claim_info (serial varchar(50),partnumber varchar(50),invoice varchar(50),invoicedate varchar(50))

    create table company_info (company_name varchar(50),address1 varchar(50)

    ,address2 varchar(50),city varchar(50),state varchar(50),zip varchar(50))

    create table user_info ([user_name] varchar(50),[user_id] varchar(50),phone varchar(50)

    ,email varchar(50),ip_address varchar(50))

    /******************************************************/

    Below is the code which takes datafrom xml file and inserts into database tables

    /***********************************************************/

    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 c:\abcd.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 '-------------------------------'

    DROP TABLE #tmpFileLines

    --Preparing for calling OPENXML

    DECLARE @hDoc int

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText

    --Inserting using OPENXML

    /************Company info****************************************/

    INSERT INTO [company_info]([company_name], [address1], [city], [state], [zip])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/company_info', 2)

    with (company_name nvarchar(255),

    address1 nvarchar(255),

    city nvarchar(255),

    state nvarchar(255),

    zip nvarchar(255))

    /******************Company Info****************************/

    INSERT INTO [company_info]([company_name], [address1], [address2], [city], [state], [zip])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/customer/company_info', 2)

    with (company_name nvarchar(255),

    address1 nvarchar(255),

    address2 nvarchar(255),

    city nvarchar(255),

    state nvarchar(255),

    zip nvarchar(255))

    /******************Claim_info******************************/

    INSERT INTO [claim_info]([serial], [partnumber], [invoice], [invoicedate])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/customer/claim_info', 2)

    with (serial nvarchar(255),

    partnumber nvarchar(255),

    invoice nvarchar(255),

    invoicedate nvarchar(255)

    )

    /***************************Reseller****************************/

    INSERT INTO [reseller]([qd], [company_name], [user_name], [customer_company_name])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/customer', 2)

    with (qd nvarchar(255) '../qd',

    company_name nvarchar(255) '../company_info/company_name',

    [user_name] nvarchar(255) '../user_info/user_name',

    customer_company_name nvarchar(255) 'company_info/company_name'

    )

    /***************Customer_Serial*********************************/

    INSERT INTO [customer]([company_name], [serial])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/customer', 2)

    with (

    customer_company_name nvarchar(255) 'company_info/company_name',

    serial nvarchar(255) 'claim_info/serial'

    )

    /******************User Info****************************/

    INSERT INTO [user_info]([user_name], [user_id], [phone], , [ip_address])

    SELECT *

    FROM OPENXML(@hDOC, '/claim_submission/reseller/user_info', 2)

    with (user_name nvarchar(255),

    user_id nvarchar(255),

    phone nvarchar(255),

    email nvarchar(255),

    ip_address nvarchar(255)

    )

    EXEC sp_xml_removedocument @hdoc

    GO

    SET NOCOUNT OFF

    /*******************************************************/

    this code will work best for small documents (< 8000 bytes).
    how to overcome this

    Regards

    Arvinder
    arvinder-khosla.blogspot.com

  • June 28, 2006 at 6:22 am

    #646338

    You've not really explained what it is you're trying to do, but maybe these links will help...?

    http://www.perfectxml.com/articles/xml/importxmlsql.asp

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=217076

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 3, 2006 at 2:36 am

    #647150

    I understood what arvinder saying, because I am also facing the same problem! He is taken that code from http://www.perfectxml.com/articles/xml/importxmlsql.asp

    The maximum characters that you can pass while working in query analyzer is 8000 characters (varchar limit).  This is the problem.

    ==================================

    Error

    XML parsing error: The following tags were not closed: book, DATE.

    -------------------------------

    Bytes read from the file:

    8000

    ==================================

      I am trying to work on the 'OPENXML and External XML File'  This code works only for small xml files, because it takes only 8000 bytes(DECLARE @strXMLText nvarchar(4000)) It wont support more than 4000. I have more than 1million records to update to sqlserver so it gives me error.

    Anyone knows to increase  varchar llimit?

    Ajjacob

  • July 3, 2006 at 10:34 am

    #647266

    The other link I posted said: "Drop it in the too hard basket - I'm going to do it another way....". You might, therefore, both be out of luck!

    I recall seeing an article on the web that I thought dealt with this, but I can't find it for the life of me. Maybe I dreamt it. Sorry

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • October 19, 2006 at 1:49 pm

    #666925

     

    here's microsoft's solution:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp

     

    hope it helps...........

Viewing 5 posts - 1 through 4 (of 4 total)

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