CODEWITHSUNDEEP
xpathsql-server-2008-r2
MirlvsMaximvs
MirlvsMaximvs

Reputation: 1483

Can't get data with a xpath query

how could i get only the rows where the ProcedureID = 6104 in my xml database field?

<CDirData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://Fnet.ESB.Schemas.CentroDirectivo.CDirData">
  <ProcedureData xmlns="">
    <ProcedureId>6104</ProcedureId>
    <CentroDirectivo>SGRP</CentroDirectivo>
  </ProcedureData>
  <SolicitudData xmlns="">
    <SolicitudId>MFom635230432391710791</SolicitudId>
    <Status>Iniciado</Status>

I've been trying something like 

WITH XMLNAMESPACES (
    'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
    'http://www.w3.org/2001/XMLSchema' AS "xsd",
    'http://Fnet.ESB.Schemas.CentroDirectivo.CDirData' AS "de") 

SELECT [Message].value(
'(/de:CDirData/de:ProcedureData/de:ProcedureId)[1]', 'nvarch

but always returns null rows ...

Thanks in advance

Upvotes: 1

Views: 39

Answers (1)

TGH
TGH

Reputation: 39268

The complication here is the default namespace defined at the root.

One workaround is to define your query in terms of local-name

//*[local-name()='ProcedureId' and text()='6104']

Upvotes: 2

Related Questions