How to extract information from xml database field?

During development you might find some objects are stored in database as single xml string (ASP.NET Profiles).

For examle:

<?xml version=”1.0″ encoding=”utf-16″?>

<Address xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” 

 AddressID=”0″ 

 UserName=”" 

 FirstName=”Joe” 

 LastName=”Stevens” 

 Phone=”" 

 Email=”" 

 Address1=”1235 West Lane” 

 Address2=”" 

 City=”Miami” 

 StateOrRegion=”FL” 

 Zip=”33179″ 

 Country=”" 

 CreatedOn=”2007-08-17T23:52:10″ 

 CreatedBy=”" 

 ModifiedOn=”2007-08-17T23:52:10″ 

 ModifiedBy=”">

</Address>

If you need to extract specific information from this field you can use xml paths.

For example for City:

SELECT CONVERT(xml, dbo.aspnet_Profile.PropertyValuesString ).value('(/Address/@City)[1]', 'nvarchar(max)') AS City


Posted on Sunday, February 10, 2008 by | Comments (1) | Add Comment

Comments

Gravatar

Re: How to extract information from xml database field?

Whoever edits and publishes these atrciles really knows what they're doing.

Posted on 3/26/2012 1:40:34 PM by Agung #

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5