Extract email address embedded in a xml string using a select query in SQL

[SQLnewbie2012]

I have a table in SQL with the following columns and data:

First_name is Joe
Last_Name is Smith
Zipcode is 12345
Join_date is 05012011
xmldata is

<data>Network Admin</data>
    <definition_field_id>beaa46b0-d1bb-408a-a5cc-56764f8562b6</definition_field_id>
    <validation_status>Valid</validation_status>
  
<field>
    <data>Idontknow_noidea@noemail.net</data> 
    <definition_field_id>a1cf1da8-7924-428b-b6b2-4315402738d9</definition_field_id>
    <validation_status>Valid</validation_status>

How do I write a select query in SQL that will extract the email in a SQL query so my results can look like:

Joe, Smith, 12345, 05012011, Idontknow_noide a@noemail.net for the First_name, Last_Name, Zipcode, Join_date and email headers respectively?

Thanks

[ck9663]

If you need the XML for something else, I suggest you create a function that can parse it and return your desired element. If you don't need it for anything else, convert it to varchar(max) and look for the email tag, then just use substring to get the email address.

Happy Coding!!!


~~ CK

[SQLnewbie2012]

HI CK,I tried this but it doen't give me the whole email, cuts out the domain

Select top2
reverse(substri ng(reverse(subs tring(cast(xmld ata as varchar(Max)),0 ,charindex('@', cast(xmldata as varchar(Max)))+ len('<data>'))) ,0,
patindex('%>%', reverse(substri ng(cast(xmldata as varchar(Max)),0 ,charindex('@', cast(xmldata as varchar(Max)))+ len('.')))))) as email_id
from mytable

Where am I going wrong?

[ck9663]

Use PATINDEX to locate the start of <data> then use it again to local </data>. Using those numbers, you can use substring to get the string inside the data tags. No need to reverse.

Happy Coding!!!


~~ CK