How to extract information from xml database field?
During development, you might find some objects are stored in the database as a single XML string (ASP.NET Profiles).
For example:
<?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 a City:
SELECT CONVERT(xml, dbo.aspnet_Profile.PropertyValuesString ).value('(/Address/@City)[1]', 'nvarchar(max)') AS City
Posted on February 10, 2008 by Viktar Karpach
