Read XML data from a table using SQL query

Type 1:

CREATE

function dbo.test (@XML as xml)

returns

xml

as

begin

return

@XML.query('/root/row2/value');

end

　

　

create

table #test (xml_data xml)

--drop table #test

　

insert

into #test

select

'

<root>

　 <row1>

　　　 <value>11</value>

　 </row1>

　 <row2>

　　　 <value>22</value>

　 </row2>

</root>

'

union

all

select

'

<root>

　 <row1>

　　　 <value>33</value>

　 </row1>

　 <row2>

　　　 <value>44</value>

　 </row2>

</root>'

　

select

　 xml_data,dbo.[test](xml_data) from　 #test

　

　

　

Type 2:

declare

@MyTable table (MyColumnName xml)

insert

into @MyTable

select

'<fields>

<field id="StudentName">John Smith</field>

<field id="StudentID">1310021003</field>

<field id="SchoolName">Little Kid Elementary School</field>

</fields>'

union all

select

'<fields>

<field id="StudentName">John Smith</field>

<field id="StudentID">2343343434</field>

<field id="SchoolName">Little Kid Elementary School</field>

</fields>'

　

select

　　　 MyColumnName

.value('(fields/field[@id="StudentID"]/text())[1]', 'nvarchar(max)') as StudentID

from

@MyTable