Extract email address embedded in a xml string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extract email address embedded in a xml string

Author

Topic

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 11:24:41

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
data in the xmldata column 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_noidea@noemail.net for the First_name, Last_Name, Zipcode, Join_date and email headers respectively?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-06 : 11:46:50

does the data tag repeats more than once in the column?

IF NOT:

try this :

declare @v varchar (400)
set @v=' 
<data>Idontknow_noidea@noemail.net</data> 
<definition_field_id>a1cf1da8-7924-428b-b6b2-4315402738d9</definition_field_id> 
<validation_status>Valid</validation_status> '

select substring(@v, charindex('<data>',@v)+6,CHARINDEX('</data>',@v)-charindex('<data>',@v)-6) as Email

result:
Idontknow_noidea@noemail.net

--------------------------
Joins are what RDBMS's do for a living

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 12:16:38

There are about 100K rows in the data, with various emails buried in the xml strings. Will set @v='xmlcolumn' work?

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-06 : 12:23:17

quote:
Originally posted by mbwakali2k7

There are about 100K rows in the data, with various emails buried in the xml strings. Will set @v='xmlcolumn' work?

in the column XML, how many <data> tags will be for each row?

--------------------------
Joins are what RDBMS's do for a living

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 12:33:59

the xml posted is not wellformed
i cant see closing tag for <field>

also there's no root node

is the datatype really xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 12:45:43

There are 28 <data> and 28 </data>

quote:
Originally posted by xhostx

quote:
Originally posted by mbwakali2k7

There are about 100K rows in the data, with various emails buried in the xml strings. Will set @v='xmlcolumn' work?

in the column XML, how many <data> tags will be for each row?

--------------------------
Joins are what RDBMS's do for a living

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 12:49:13

Yes it is xml, I did shorten it as it had a lot of other information that I don’t need. There are street addresses, telephone etc and each section is preceded by <data> and ends with </data>. The programmer took all the demographic information and built an xml string.

quote:
Originally posted by visakh16

the xml posted is not wellformed
i cant see closing tag for <field>

also there's no root node

is the datatype really xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 12:53:37

quote:
Originally posted by mbwakali2k7

Yes it is xml, I did shorten it as it had a lot of other information that I don’t need. There are street addresses, telephone etc and each section is preceded by <data> and ends with </data>. The programmer took all the demographic information and built an xml string.

quote:
Originally posted by visakh16

the xml posted is not wellformed
i cant see closing tag for <field>

also there's no root node

is the datatype really xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

doesnt it have a root node on top?
so each column has multiple <data> nodes without any root?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 12:58:22

That is correct. No nodes.

quote:
Originally posted by visakh16

quote:
Originally posted by mbwakali2k7

Yes it is xml, I did shorten it as it had a lot of other information that I don’t need. There are street addresses, telephone etc and each section is preceded by <data> and ends with </data>. The programmer took all the demographic information and built an xml string.

quote:
Originally posted by visakh16

the xml posted is not wellformed
i cant see closing tag for <field>

also there's no root node

is the datatype really xml?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

doesnt it have a root node on top?
so each column has multiple <data> nodes without any root?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 13:03:04

the way you have it currently has lots of intermixed data,definition_field_id and validation_status nodes. how do you think you would distinguish individual data items from them as they seem to have different data items. why not store them as different nodes like

<email>Idontknow_noidea@noemail.net</email>
<StreetAddress>address value here</StreetAddress>
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mbwakali2k7
Starting Member

6 Posts

Posted - 2012-08-06 : 13:44:05

The programmer built the data that way; I want to extract the individual pieces in the string so that one can carry out some sort of QA in the table or even have an insight of the information the table holds. Right now each data (that should really be on its own column) is preceded by <data> and succeeded by </data>. John Smith who lives in 123 Main st, NY, 10453 and email is jsmith@idontknow.net would read as
<fields>
<field>
<data>5/25/2000 12:00:00 AM</data>
<definition_field_id>7617cc60-fefd-4fe2-be2e-00cfeeecfdbe</definition_field_id>
<validation_status>Valid</validation_status>
</field>
<data>John</data>
<definition_field_id>7bc4b643-efe5-4d1d-aebe-821f2cf03d4f</definition_field_id>
<validation_status>Valid</validation_status>
</field>
<field>
<data>Smith</data>
<definition_field_id>e0852357-66d3-4897-bbaf-465fa1512a4b</definition_field_id>
<validation_status>Valid</validation_status>
</field>
<definition_field_id>a30264b0-d90a-45ca-aaec-84d3c5d272d0</definition_field_id>
<validation_status>Valid</validation_status>
<field>
<data>123 Main St</data>
<definition_field_id>43dee223-efc5-4c88-a799-5ffcee20000e</definition_field_id>
<validation_status>Valid</validation_status>
…… and so on.
The column headers are in the data between <definition_field_id> and </definition_field_id>

quote:
Originally posted by visakh16

the way you have it currently has lots of intermixed data,definition_field_id and validation_status nodes. how do you think you would distinguish individual data items from them as they seem to have different data items. why not store them as different nodes like

<email>Idontknow_noidea@noemail.net</email>
<StreetAddress>address value here</StreetAddress>
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 13:49:07

you cant reliably get data pieces from current format unless at least positions of nodes are fixed.
you need to get them in a consistent format for shredding data properly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources