Extract xml to table.

Question

Post reply

Extract xml to table.

panneermca35

Mr or Mrs. 500

Points: 504
More actions
March 7, 2016 at 4:45 am

#305466

Hi,
I have a following xml.
'<Item>
<Item customerid="101" customername="Ram" gender="Male" married="Y"/>
<Item customerid="102" customername="Sham" gender="Male" married="Y"/>
<Item customerid="103" customername="Tom" gender="Male" married="N"/>
</Item>'
I want the Result set like below.
customerid customername gender Married
101 ram Male Y
102 sham Male Y
103 tom Male N
Kindly do the needful. to extract the xml to table

Viewing 4 posts - 1 through 3 (of 3 total)

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

xanthos SSCrazy Points: 2510 More actions · Answer 1

You could try the following:

DECLARE @xml as xml

SET @xml =

'<Item>

</Item>'

SELECT T.c.value('@customerid', 'int') AS Customerid

, T.c.value('@customername', 'varchar(50)') AS Customername

, T.c.value('@gender', 'varchar(6)') AS Gender

, T.c.value('@married', 'char(1)') AS Married

FROM @xml.nodes('/Item/Item') AS T(c)

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's one way to do it:

DECLARE @xml XML ='<Item>

<Item customerid="101" customername="Ram" gender="Male" married="Y"/>

<Item customerid="102" customername="Sham" gender="Male" married="Y"/>

<Item customerid="103" customername="Tom" gender="Male" married="N"/>

</Item>'

select

row.value('@customerid', 'varchar(100)') as customerid,

row.value('@customername', 'varchar(100)') as customername,

row.value('@gender', 'varchar(100)') as gender,

row.value('@married', 'varchar(100)') as married,

row.query('.')

from @xml.nodes('/Item/Item') as xmltable(row)

--where row.value('@customerid', 'varchar(100)') = '101'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

panneermca35 Mr or Mrs. 500 Points: 504 More actions · Answer 3

Hi All,

Thanks for your replies.

I want dynamic code.

Because every time I ll pass the different number columns so I can't hard coded. Kindly help me in this