none
XML to temp table RRS feed

  • Question

  • CREATE TABLE #SampleTable
    (
        Number varchar(100),
        StartNum int
    )

    I want to parse the XML and insert into SQL SERVER 2000

    <activateNumber>
             <!--You may enter ANY elements at this point-->
             <number>1234</number>
             <StaartNumbers>
                <StartNum>234</StartNum>
             </StaartNumbers>
    </activateNumber>

    NOTE: I'm using sql server 2000

    Thanks in advance.


    Best Regards, Venkat

    Thursday, February 28, 2013 9:39 AM

Answers

  • Check this:

    CREATE TABLE #SampleTable
    (
        Number varchar(100),
        StartNum int
    )
    
    DECLARE @xml XML = '<activateNumber>
             <!--You may enter ANY elements at this point-->
             <number>1234</number>
             <StaartNumbers>
                <StartNum>234</StartNum>
             </StaartNumbers>
    </activateNumber>'
    
    DECLARE @iDoc int
    EXEC sp_xml_preparedocument @iDoc output, @xml
    
    INSERT INTO #SampleTable
    SELECT 
    	number,
    	StartNum
    FROM OPENXML(@iDoc,'/activateNumber/StaartNumbers',2)
    WITH (number INT '../number[1]', 
    		StartNum INT 'StartNum[1]')
    
    EXEC sp_xml_removedocument @iDoc
    


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011 | My FB Page

    Thursday, February 28, 2013 10:04 AM