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.caza...@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 <fox...@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-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/2e77d933-4456-4c5c-8095-148ddb165...@gmail.com
** 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.
