Post reply

Parse XML to table

  • July 22, 2013 at 3:22 pm

    #296660

    Comments posted to this topic are about the item Parse XML to table

  • August 5, 2013 at 5:24 am

    #1638568

    I tried running it on a bit of xml that stores an order on our database.

    It ran for over 6 minutes before I stopped it.

  • August 5, 2013 at 6:21 am

    #1638576

    This only works for the most of trivial samples.

    Academic and won't work for real world cases.

    Try using it on over a gig of XML data and watch it blow up.

  • August 5, 2013 at 7:26 am

    #1638592

    I just tried it on a bit of xml that had a DataLength of 13859 and it took 3:28 (3 minutes 28 seconds) to complete. I then tried it on another bit of xml with a datalength of 20030 and it took 22:47 (22 minutes) to complete and generated 993 rows. It would be nice if it were a bit quicker so it could be used in a reasonable amount of time.

  • August 5, 2013 at 7:44 am

    #1638600

    I'll add to the slowness comments... it processed 11K of XML in a little over two minutes. I tried on a 141K block and it ran for 12 minutes before I gave up.

  • August 5, 2013 at 4:41 pm

    #1638768

    Thanks all for the feedback. Every environment is different so it's hard to anticipate what kind of loads such a process might need to handle. In my use it was for relatively small XML files used by a web service.

    I think the main problem is that it uses several levels of recursion within the CTEs. I'll see what I can do to streamline the process. Any suggestions are welcome. I'm always open to improving the code.

     

  • April 8, 2015 at 1:51 am

    #1789441

    This is a nice bit of code on smaller chunks of XML.

    It might also be worth adding exec "sp_xml_removedocument @idoc" to the end of the procedure to free up memory.

  • April 8, 2015 at 11:43 am

    #1789642

    Not working for me.

    SELECT @@VERSION

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

    May 14 2014 18:34:29

    Copyright (c) Microsoft Corporation

    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    When trying an example from the post it gives:

    Msg 217, Level 16, State 1, Procedure ParseXMLtoTable, Line 63

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    PS. Nevermind. I just run the whole code at once and examples just became a part of the procedure itself.

  • April 8, 2015 at 4:48 pm

    #1789737

    The code has several issues.

    - Missing sp_removedocument means there is SQL memory leak. sp_PrepareDocument is no longer recommended.

    - Placing a string into a XML type variable parses it automatically, there was no need to use sp_preparedocument. This was done several places with the overhead of parsing done each time.

    - Having an input parameter of XML type and passing in an invalid XML string (due to the automatic parsing) can be hard to track down. Better to have the input parameter be nvarchar(max) then convert to XML variable inside the SP where invalid XML can be handled.

  • April 16, 2015 at 5:15 am

    #1791280

    i am missing or not seeing the place where to puth my path

    i.e. C:\data.xml

  • April 28, 2016 at 7:11 am

    #1875231

    Thanks for the script.

Viewing 11 posts - 1 through 10 (of 10 total)

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