Why does my T-SQL xmldata.query not find the data I want?

Question

I have the following query that works. It returns the data I want.

SELECT *    
FROM pennlink.dbo.logentry WITH (nolock)
WHERE CAST(xmldata.query('/ACORD/SignonRq/SignonPswd/CustId/CustPermId/text()') AS varchar(max)) LIKE '%test123%' 
  AND TYPE = 'request' 
  AND datesent > '10/27/2014' 
  AND datesent < '10/28/2014

Below is the format of the XML that the above query finds.

<ACORD xmlns:q1="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/">
  <SignonRq>
    <SignonPswd>
      <CustId>
        <CustPermId>test123</CustPermId>
      </CustId>
    </SignonPswd>

In the xmldata column there is also XML that is formatted differently than the above.

The node is named: CustLoginId. Plus some namespaces.

See below:

<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
  <SignonRq>
    <SignonPswd>
      <CustId>
        <CustLoginId>test456</CustLoginId>
      </CustId>
      <CustPswd>

I modified my query to use the CustLoginId, but no data is returned.

See below.

SELECT *    
FROM pennlink.dbo.logentry WITH (nolock)
WHERE CAST(xmldata.query('/ACORD/SignonRq/SignonPswd/CustId/CustLoginId/text()') AS varchar(max)) LIKE '%test456%'  
  AND TYPE = 'request' 
  AND datesent > '10/27/2014' 
  AND datesent < '10/28/2014'

What am I doing wrong?

I assume it has something to do with the namespaces. I found a few reference to how to query using the namespaces but I can't get the syntax correct.

Please help.

Thank you

Answer 1

You should use SQLXML methods in your query. Something like this.

declare @tbl table(id int, xmldata xml)
insert @tbl(id,xmldata)
values(1, N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
  <SignonRq>
    <SignonPswd>
      <CustId>
        <CustLoginId>test456</CustLoginId>
      </CustId>
      <CustPswd/>
    </SignonPswd>  
  </SignonRq>    
</ACORD>'),
(2, N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
  <SignonRq>
    <SignonPswd>
      <CustId>
        <CustLoginId>test123</CustLoginId>
      </CustId>
      <CustPswd/>
    </SignonPswd>  
  </SignonRq>    
</ACORD>')

;with xmlnamespaces(default 'http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/')
select id, t.v.query('(//SignonRq)[1]') query
,t.v.value('(//CustId/CustLoginId)[1]','varchar(50)') loginid
from @tbl
cross apply xmldata.nodes('ACORD') t(v) --convert xml data to table
where t.v.value('(//CustId/CustLoginId)[1]','varchar(50)')='test456'

Answer 2

declare @x xml = N'<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" RatingSvcVersion="3.0">
  <SignonRq>
    <SignonPswd>
      <CustId>
        <CustLoginId>test456</CustLoginId>
      </CustId>
      <CustPswd/>
    </SignonPswd>  
      </SignonRq>    
      </ACORD>'

select @x, @x.query('//*')--the default xmlns www.accord to each element


select 'exists', 
    @x.value('declare default element namespace "http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/"; (ACORD/SignonRq/SignonPswd/CustId/CustLoginId)[1]', 'varchar(100)') as test
where @x.exist('declare default element namespace "http://www.ACORD.org/standards/PC_Surety/ACORD1/xml/";
/ACORD/SignonRq/SignonPswd/CustId/CustLoginId[text()="test456"]') = 1;