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.
x
<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
Advertisement
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;