none
How to convert the XML to temp table

    Question

  • Hi all,

    I prepared an xml through C# XML serializer. How to change the below xml to temp table 

    '<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <int>63</int>

    <int>13</int>

    <int>12</int>

    <int>15</int>

    <int>16</int>

    <int>35</int>

    </ArrayOfInt>' 

    please help..

    Thursday, March 01, 2012 1:19 PM

Answers

  • Try this:

    DECLARE @xml XML
    
    SET @xml = 
    '<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <int>63</int>
      <int>13</int>
      <int>12</int>
      <int>15</int>
      <int>16</int>
      <int>35</int>
    </ArrayOfInt>' 
    
    SELECT a.c.value('.', 'INT')
    FROM @xml.nodes('ArrayOfInt/int') a(c)

    • Marked as answer by Manee kantha Friday, March 02, 2012 4:45 AM
    Thursday, March 01, 2012 1:28 PM
    Answerer

All replies

  • Try this:

    DECLARE @xml XML
    
    SET @xml = 
    '<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <int>63</int>
      <int>13</int>
      <int>12</int>
      <int>15</int>
      <int>16</int>
      <int>35</int>
    </ArrayOfInt>' 
    
    SELECT a.c.value('.', 'INT')
    FROM @xml.nodes('ArrayOfInt/int') a(c)

    • Marked as answer by Manee kantha Friday, March 02, 2012 4:45 AM
    Thursday, March 01, 2012 1:28 PM
    Answerer
  • HI,

        Thank u very much. Its working very fine.  Can we able to achieve the same using OpenXML.

    Friday, March 02, 2012 4:54 AM
  • You can do that with OPENXML but can you tell me why you want to use OPENXML?

    OPENXML can only take a single XML document as its input whereas the nodes method can be used against a table with an XML column, ie OPENXML is single row-processing, using the methods of the XML datatype, eg nodes, exist, value, query is set-processing.

    There can be problems with OPENXML, eg it can take up to 1/8th of your server memory when opening a document.  If you don't use sp_xml_removedocument, you can get out-of-memory problems or even memory leaks.  I have seen some anecdotal evidence it is faster for larger documents, but haven't really seen this myself.

    If you weigh up all of these and still want to use OPENXML, then try this:

    DECLARE @xml NVARCHAR(MAX)
    
    SET @xml = 
    '<ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <int>63</int>
      <int>13</int>
      <int>12</int>
      <int>15</int>
      <int>16</int>
      <int>35</int>
    </ArrayOfInt>' 
    
    DECLARE @hDoc int
    
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 
    
    SELECT *
    FROM OPENXML( @hDoc, 'ArrayOfInt/int', 1 ) 
    WITH ( x INT '.' )
    
    EXEC sp_xml_removedocument @hDoc

    Friday, March 02, 2012 10:04 AM
    Answerer
  • Hi,

    OPENXML is the best solution

    also have a look at CURSORS for data transversal

    Thursday, March 15, 2012 10:02 AM