KARPACH

WEB DEVELOPER BLOG

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

Comments

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

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