How to extract data from a xml field

Question

Post reply

How to extract data from a xml field

cc1960

Valued Member

Points: 56
More actions
September 8, 2010 at 2:15 pm

#238035

Hi folks.
I have a field of type xml.
It contains data of the following format <a>1234</b</a>
I want to extract the number as a int in a query.
select XmlField.query('/a/b') from myTable
returns
1000
1001
1002
How do I specify the query to return the values as
1000
1001
1002
Thanks
Christian

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

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

sql-programmers SSC-Addicted Points: 484 More actions · Answer 1

Try this,

CREATE TABLE tblXML (ID INT PRIMARY KEY, xmlVal XML not null)

insert into tblXML values(2,'<a>1234</a><a>222</a><a>333</a>')

select * from tblXML

SELECT x.query('b').value('.','varchar(100)') as Product

FROM tblXML CROSS APPLY xmlVal.nodes('/a') AS Tref(x)

Result:

Product

1234

222

333

SQL Sever Performance tuning

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

This post shouldn’t be in the SQL Server 2000/7, because those versions don’t support Xquery. Having said that, the method query returns an untyped xml (just as you discovered). The method value returns the value of the element or attribute, but it can return only one value. In order to get the values for more then one node, you’ll need to work with the node method combined with the value method. Below is a small example:

declare @xml xml

set @xml =

'<root>

<a>

1234

</a>

<a>

5678

</a>

</root>'

select tbl.col.value('.','int')

from @xml.nodes('/root/a/b') tbl (col)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

cc1960 Valued Member Points: 56 More actions · Answer 3

cc1960

Valued Member

Points: 56

September 10, 2010 at 1:32 am

#1219016

Thanks that was exactly what I was looking for.