Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to convert the XML to temp table

답변됨 How to convert the XML to temp table

  • Thursday, March 01, 2012 1:19 PM
     
     
    Sign In to Vote
    0

    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..

     

All Replies

  • Thursday, March 01, 2012 1:28 PM
    Answerer
     
     Answered Has Code
    Sign In to Vote
    0

    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 Manikantha Friday, March 02, 2012 4:45 AM
    •  
     
  • Friday, March 02, 2012 4:54 AM
     
     
    Sign In to Vote
    0

    HI,

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

     
  • Friday, March 02, 2012 10:04 AM
    Answerer
     
      Has Code
    Sign In to Vote
    0

    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

     
  • Thursday, March 15, 2012 10:02 AM
     
     
    Sign In to Vote
    0

    Hi,

    OPENXML is the best solution

    also have a look at CURSORS for data transversal