Querying An XML Field In SQL

• gadamson

  SSC Veteran

  Points: 296

  More actions

  March 11, 2011 at 10:07 am

  #72958

  Helllo,

  Working in SQL 2008

  Sample data from sql xml field (that is inserted into temp table #test)

  <Listing xmlns="http://rets.org/xsd/Syndication/2008-03">

  <Address>...

  <ListPrice>327900</ListPrice>

  Query I'm Using to Grab "ListPrice"

  Select

  p.value('/Listing[1]/@ListPrice[1]', 'varchar (100)')As ListPrice

  FROM #test CROSS APPLY ListingNode.nodes('/Listing[1]/@ListPrice[1]') t(p)

  Problem:

  I'm not getting any results. Any thoughts on what I'm doing wrong?

  Thanks, GA

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  March 11, 2011 at 10:57 am

  #1297792

  A couple problems. You have a default namespace which you need to declare. I prefer using the "with xmlnamespaces" statement. Next, you are looking for a ListPrice element not a ListPrice attribute. @ListPrice returns the ListPrice attribute of the Listing element. ListPrice (with no @ prefix) returns the ListPrice element.

  declare @t table (ListingNode xml);

  insert into @t values(

  '<Listing xmlns="http://rets.org/xsd/Syndication/2008-03">

  <ListPrice>327900</ListPrice>

  </Listing>')

  ; with xmlnamespaces (default 'http://rets.org/xsd/Syndication/2008-03')

  Select

  p.value('/Listing[1]/ListPrice[1]', 'varchar (100)')As ListPrice

  FROM @t tmp CROSS APPLY tmp.ListingNode.nodes('/Listing[1]/ListPrice[1]') t(p)

  Russel Loski, MCSE Business Intelligence, Data Platform

• gadamson

  SSC Veteran

  Points: 296

  More actions

  March 11, 2011 at 11:04 am

  #1297793

  Thank you so much Russell. Your comments/advice were spot on. Very much appreciated.

• alexisalley1

  Newbie

  Points: 7

  More actions

  March 23, 2011 at 3:58 am

  #1302348

  Thanks Dear .your solution is so much helpful.

  ---------------------------------

  Office Cleaning | Window Cleaning