CODEWITHSUNDEEP
sqlsql-serverxmlxpath
adev
adev

Reputation: 58

Xpath SQL Server query

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>

Upvotes: 2

Views: 1894

Answers (1)

marc_s
marc_s

Reputation: 754230

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.

Upvotes: 2

Related Questions