Xpath SQL Server query

Question

I would like select the value of Type where the xml below is in an xml typed column in a table in a SQL Server 2005 database.

<Something xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Id xmlns="http://something.com/">1921</Id>
  <Date xmlns="http://something.com/">1901-01-01T00:00:00Z</Date>
  <Schedule xmlns="http://something.com/">Region</Schedule>
  <Filters xmlns="http://something.com/">
    <Data>
      <Id>99999</Id>
      <Name>CS</Name>
      <Type>SomeType</Type>
    </Data>
  </Filters>
</Something>

marc_s · Accepted Answer · 2012-01-26 16:58:24Z

2

Try something like this:

;WITH XMLNAMESPACES ('http://something.com/' AS xn)
SELECT
    XmlType = XmlColumn.value('(Something/xn:Filters/xn:Data/xn:Type)[1]', 'varchar(50)') 
FROM 
    dbo.YourTable

Basically, your root element <Something> isn't part of any explicit XML namespace - but any of the elements under <Filters> are - so therefore, you need to qualify those with an appropriate XML namespace prefix to get at the data inside the XML element.

answered Jan 26, 2012 at 16:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Xpath SQL Server query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related