Need a where clause for an XML Node in a SQL Server 2019 stored procedure

I have 1.5 million XML documents stored in a SQL Server 2019 database and I need to have a where clause that has multiple nodes in a stored procedure.

<PROJECTS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <APPLICATION_ID>1015</APPLICATION_ID>
    <ORG_STATE>SC</ORG_STATE>
    <ORG_CITY>Charleston</ORG_CITY>
    <ORG_ZIPCODE>29407</ORG_ZIPCODE>
    <PIS>
      <PI>
        <PI_NAME>BO, LEO (contact)</PI_NAME>
        <PI_ID>9983950 (contact)</PI_ID>
      </PI>
      <PI>
        <PI_NAME>KUZ, BEN I</PI_NAME>
        <PI_ID>1862593</PI_ID>
      </PI>
    </PIS>
    <PROJECT_START>08/15/2019</PROJECT_START>
    <PROJECT_END>05/31/2024</PROJECT_END>
    <INDIRECT_COST_AMT>103034</INDIRECT_COST_AMT>
    <TOTAL_COST>638854</TOTAL_COST>
    <TOTAL_COST_SUB_PROJECT />
  </row>
</PROJECTS>

I need to pull all XML files where PI_ID equals 9983950. The number of PI’s in the PIS node could be one or 5.

I’m using this code:

SELECT TOP 100 
    [APPLICATION_ID], [FileName], [XMLData],
    nref.value('ORG_CITY[1]', 'VARCHAR(30)') as ORG_CITY  
FROM
    [NIH_EXPORTER].[dbo].[ADMIN_Exporter_Files_XML] 
CROSS APPLY
    XMLData.nodes('//row[1]') AS R(nref)
WHERE 
    nref.value('ORG_CITY[1]', 'VARCHAR(30)') = 'Charleston'

when I need the city but I’m not sure how to find the value when there are multiple nodes

Answer

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<PROJECTS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <row>
        <APPLICATION_ID>1015</APPLICATION_ID>
        <ORG_STATE>SC</ORG_STATE>
        <ORG_CITY>Charleston</ORG_CITY>
        <ORG_ZIPCODE>29407</ORG_ZIPCODE>
        <PIS>
            <PI>
                <PI_NAME>BO, LEO (contact)</PI_NAME>
                <PI_ID>9983950 (contact)</PI_ID>
            </PI>
            <PI>
                <PI_NAME>KUZ, BEN I</PI_NAME>
                <PI_ID>1862593</PI_ID>
            </PI>
        </PIS>
        <PROJECT_START>08/15/2019</PROJECT_START>
        <PROJECT_END>05/31/2024</PROJECT_END>
        <INDIRECT_COST_AMT>103034</INDIRECT_COST_AMT>
        <TOTAL_COST>638854</TOTAL_COST>
        <TOTAL_COST_SUB_PROJECT/>
    </row>
</PROJECTS>');
-- DDL and sample data population, end

DECLARE @PI_ID VARCHAR(20) = '9983950';

SELECT ID
    , xmldata.value('(/PROJECTS/row/ORG_CITY/text())[1]', 'VARCHAR(30)') as ORG_CITY
FROM @tbl 
WHERE xmldata.exist('/PROJECTS/row/PIS/PI/PI_ID[contains(./text()[1], sql:variable("@PI_ID"))]') = 1;