RE: Using XML String as part of query in SQL Server

I got it doing it like this:

SELECT iMemoNumber, DAGREEMENTDATE FROM Agreements WHERE iMemoNumber IN ( SELECT imemo = x.Rec.query('./imemonumber').value('.','integer') -- xmlArray.value('@imemonumber ','integer') FROM @Array.nodes('/VFPData/c_agreements') AS x(Rec) -- TEMPTABLE(xmlArray) )

Thanks for the help

On 26 November 2019 18:32:12 GMT-04:00, Frank Cazabon frank.cazabon@gmail.com wrote:

Thanks Darren,

I'm not getting anything returned and don't understand the syntax yet to be sure how to fix your example.

I've tried this:

declare @array xml = '<?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>17153</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>' /* SELECT iMemoNumber FROM Agreements WHERE iMemoNumber IN ( */ SELECT xmlArray.value('@imemonumber ','integer') FROM @Array.nodes('/c_agreements/imemonumber') AS TEMPTABLE(xmlArray) --)

And get nothing returned.

If I change it to this (add in VFPData):

SELECT xmlArray.value('@imemonumber ','integer') FROM @Array.nodes('/VFPData/c_agreements/imemonumber') AS TEMPTABLE(xmlArray)

I get 5 rows back but they are all null.

Any ideas what's wrong?

On 26 November 2019 15:58:39 GMT-04:00, Darren foxdev@ozemail.com.au wrote:

...

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?

--

Frank.

Frank Cazabon

[excessive quoting removed by server]