Advertisements
Home > SQL Server Questions, XML > XML to Table – MSDN TSQL forum

XML to Table – MSDN TSQL forum


–>Question:

I’m using sql server 2000 and here is my target table:

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

I want to parse the XML and insert into the above table:

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

 

–> My Answer:

Check the code below:

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

 

Ref Link.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: