sql - Extract value from XML not working with querying nodes or value

Question

I'm working in SQL Server 2016, and I'm trying to extract the <Org>Insurance Technologies Corporation</Org> value from the XML below.

I've tried querying the value using nodes and value in my tsql code, but to no avail as I keep getting a NULL value for the Rater column.

I'm not sure what I'm doing wrong in either case. Any help/direction would be appreciated. Thanks.

Here is my XML:

<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <SignonRq>
    <SignonTransport>
      <SignonRoleCd>Agent</SignonRoleCd>
      <CustId>
        <SPName>turborater.com</SPName>
        <CustLoginId>99999</CustLoginId>
      </CustId>
    </SignonTransport>
    <ClientDt>2019-07-05T11:05:36</ClientDt>
    <CustLangPref>EN-US</CustLangPref>
    <ClientApp>
      <Org>Insurance Technologies Corporation</Org>
      <Name>ITC.Insurance.EmpowerAnnSvngsRR.TX</Name>
      <Version>1.0.0.0</Version>
    </ClientApp>
  </SignonRq>
 </ACORD>

Here is my tsql code using nodes:

SELECT * 
FROM (
    SELECT rtr.BridgedDate, rtr.RealTimeRequestSysId, rtr.QuoteId, rtr.AgentId, rtr.XmlRequest
        , x.m.value('(Org)[1]', 'varchar(max)') as Rater
    FROM dbo.AUT_RealTimeRequest rtr
        OUTER APPLY rtr.XmlRequest.nodes('ACORD/SignonRq/ClientApp') x(m)
    WHERE rtr.BridgedDate BETWEEN '2020-11-01 00:00:00.000' AND '2020-11-30 23:59:59.997'
) y
WHERE y.AgentId = 3
AND y.QuoteId IS NOT NULL
ORDER BY y.QuoteId asc, y.BridgedDate asc;

Here is my tsql code using value:

SELECT rtr.BridgedDate, rtr.XMLRequest, 
rtr.XMLRequest.value('(/ACORD/SignonRq/ClientApp/Org)[1]','varchar(max)') as RaterValue
FROM dbo.AUT_RealTimeRequest rtr
WHERE rtr.QuoteId IS NOT NULL
AND rtr.AgentId = 3 --21001
AND rtr.BridgedDate BETWEEN '2020-11-01 00:00:00.000' AND '2020-11-30 23:59:59.997'
ORDER BY rtr.QuoteId asc, rtr.BridgedDate asc;

Answer 1

You're ignoring the XML namespace present in your XML data:

<ACORD xmlns="http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/" 
       *****************************************************************
       This is the "default" XML namespace for this XML data

Try this:

WITH XMLNAMESPACES(DEFAULT 'http://www.ACORD.org/standards/PC_Surety/ACORD1.11.0/xml/')
    SELECT 
        rtr.BridgedDate, rtr.XMLRequest, 
        rtr.XMLRequest.value('(/ACORD/SignonRq/ClientApp/Org)[1]', 'VARCHAR(50)') AS RaterValue
    FROM 
        dbo.AUT_RealTimeRequest rtr
    WHERE 
        rtr.QuoteId IS NOT NULL
        AND rtr.AgentId = 3 --21001
        AND rtr.BridgedDate BETWEEN '2020-11-01 00:00:00.000' AND '2020-11-30 23:59:59.997'
    ORDER BY 
        rtr.QuoteId ASC, rtr.BridgedDate ASC;