Not tested but something similar to ...
CREATE PROCEDURE [dbo].[spFindByXML] ( @Array xml ) AS
SET NOCOUNT ON; ---------- SELECT iMemoNumber, AgreementDate, Amount FROM Agreements WHERE iMemoNumber IN (SELECT xmlArray.value('@imemonumber ','integer') FROM @Array.nodes('/c_agreements/imemonumber') AS TEMPTABLE(xmlArray)) ---------- SET NOCOUNT OFF;
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Wednesday, 27 November 2019 5:16 AM To: profoxtech@leafe.com Subject: Using XML String as part of query in SQL Server
Hi,
I need to write an SQL Server Stored Procedure or just a simple SELECT Query that accepts an XML string which is basically a list of IDs that I want to use to filter data from a table.
Example XML (created using CURSORTOXML):
<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="mySchema.xsd"> <c_agreements> <imemonumber>1</imemonumber> </c_agreements> <c_agreements> <imemonumber>2</imemonumber> </c_agreements> <c_agreements> <imemonumber>3</imemonumber> </c_agreements> <c_agreements> <imemonumber>4</imemonumber> </c_agreements> <c_agreements> <imemonumber>5</imemonumber> </c_agreements> </VFPData>
I then need to:
SELECT iMemoNumber, AgreementDate, Amount
FROM Agreements
WHERE iMemoNumber IN (<<the XML list>>)
Or maybe
SELECT iMemoNumber, AGreementDate, Amount
FROM Agreements
INNER JOIN << my XML data on the iMemoNumber >>
My Google search terms have failed to produce what I am looking for.
Anybody have the solution?