Reading xml child node data

Question

Post reply

Reading xml child node data

sql_novice_2007

Hall of Fame

Points: 3307
More actions
June 29, 2010 at 12:37 pm

#220643

declare @x xml
set @x = '<ROOT>
<Createstudent>
<id>30</id>
<classId>1</classId>
<lastname>ln</lastname>
<midname>mn</midname>
<firstname>fn</firstname>
<degree>ms</degree>
<address>
<city>false</city>
<state>false</state>
<zipcode>false</zipcode>
</address>
</Createstudent>
</ROOT>'
select
T.c.value('id[1]', 'int') as id,
T.c.value('classId[1]', 'int') as classid,
T.c.value('lastname[1]', 'varchar(50)') as lastname,
T.c.value('midname[1]', 'varchar(50)') as midname,
T.c.value('firstname[1]', 'varchar(50)') as firstname,
T.c.value('degree[1]', 'varchar(50)') as degree,
T.c.value('city[1]', 'varchar(50)') as city,
T.c.value('state[1]', 'varchar(50)') as state,
T.c.value('zipcode[1]', 'varchar(50)') as zipcode
from
@x.nodes('//Createstudent') T(c)
in the above code I am able to parse the data until the node degree. But not able to parse the data for the child node 'address'.
How can I do this?
Thanks.

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

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

thecoding SSC Veteran Points: 211 More actions · Answer 1

Add address node as below

//--------------------

declare @x xml

set @x = '<ROOT>

<city>false</city>

<state>false</state>

<zipcode>false</zipcode>

</address>

</Createstudent>

</ROOT>'

select

T.c.value('id[1]', 'int') as id,

T.c.value('classId[1]', 'int') as classid,

T.c.value('lastname[1]', 'varchar(50)') as lastname,

T.c.value('midname[1]', 'varchar(50)') as midname,

T.c.value('firstname[1]', 'varchar(50)') as firstname,

T.c.value('degree[1]', 'varchar(50)') as degree,

T.c.value('address[1]/city[1]', 'varchar(50)') as city,

T.c.value('address[1]/state[1]', 'varchar(50)') as state,

T.c.value('address[1]/zipcode[1]', 'varchar(50)') as zipcode

from

@x.nodes('//Createstudent') T(c)

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 2

sql_novice_2007

Hall of Fame

Points: 3307

June 29, 2010 at 3:03 pm

#1186721

Thanks for the help. It worked for me.

sql_novice_2007 Hall of Fame Points: 3307 More actions · Answer 3

Hi I am able to parse the xml file when it has less than 1000 records. But some of the files have more than 40000 rows. So when I try to parse them I am getting memory issue and not able to parse the file.

Any ideas ?

Thanks.