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-boun...@leafe.com] On Behalf Of Frank
Cazabon
Sent: Wednesday, 27 November 2019 5:16 AM
To: profoxt...@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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/003801d5a493$e5db4d00$b191e700$@ozemail.com.au
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
