xml query

[simian336]

I am kind of new to XML data. I have a regular text field with xml data like....

Attribs

<Variables><Var><N>Email Address</N><V>tom@test.com</V></Var></Variables>
<Variables><Var><N>Email Address</N><V>Bill@test.com</V></Var></Variables>
<Variables><Var><N>Email Address</N><V>Jim@test.com</V></Var></Variables>
<Variables><Var><N>Email Address</N><V>Julie@test.com</V></Var></Variables>

I found where I can convert the field to xml by

select cast(attribs as xml) as test
from userinfo

I want a query to return just the email addresses.

tom@test.com
Bill@test.com
Jim@test.com
Julie@test.com

Thanks

Simi

 

[TheBugSlayer]

You might find this article useful:

http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[simian336]

Thanks slayer but that example uses a cross apply that would return 2 values for each row, like this....

<Person>
<Name>Simon</Name>
<Age>20</Age>
<Skills>
<Skill>Cooking</Skill>
<Skill>Cleaning</Skill>
</Skills>
</Person>

My example is a little simple I just have 2 straight fields... But I am trying to make it work....

Simi

 

[gmmastros]

simian,

You show 4 rows of sample data. Is that in 4 different rows in the database, or is this in one varchar(max) column (all four rows in the same xml data within a single row of the table)?

Declare @Temp Table(attribs varchar(max))

insert into @Temp Values('<Variables><Var><N>Email Address</N><V>tom@test.com</V></Var></Variables>')
insert into @Temp Values('<Variables><Var><N>Email Address</N><V>Bill@test.com</V></Var></Variables>')
insert into @Temp Values('<Variables><Var><N>Email Address</N><V>Jim@test.com</V></Var></Variables>')
insert into @Temp Values('<Variables><Var><N>Email Address</N><V>Julie@test.com</V></Var></Variables>')

Select Cast(attribs as xml).value('(/Variables/Var/V)[1]','varchar(100)') as test
From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

Each row is different row in the table.

Simi

 

[simian336]

Perfect George, I responed before I tried your code.

Thanks

Simi