0

Here is my query which I'm using to fetch the data from my table.

SELECT XMLQUERY('$INFO/root/database_systems/system/@name = ("SYS1","SYS2","SYS3")') 
FROM MyTable WHERE ACCT_ID = 'ID-1234';

Ok actually it is returning me true. Just because of the first value SYS1. It exists in the hierarchy but not the others. I just want to compare multiple values.

Please suggest a way to achieve this functionality. Thanks

    <root>
    <database_systems>
    <system name="SYS1">1</system>
    </database_systems>
    </root>
Superman
  • 871
  • 2
  • 13
  • 31

1 Answers1

1

If I understand correctly, you want to check for a database_systems element that contains at least all of the child elements:

<system name="SYS1">...</system>
<system name="SYS2">...</system>
<system name="SYS3">...</system>

If that is correct then you need to AND your conditions together, what you had previously was an OR:

SELECT XMLQUERY('not(empty($INFO/root/database_systems[system/@name eq "SYS1"][system/@name eq "SYS2"][system/@name eq "SYS3"]))') 
    FROM MyTable WHERE ACCT_ID = 'ID-1234';

I have used three predicates to achieve the AND, and then I check that a match was found using not(empty(...)). There are plenty of other ways to achieve this too.

adamretter
  • 3,885
  • 2
  • 23
  • 43
  • It seems like the correct solution for my problem. But let me explain you once again. 1. You are right that I need to check all such **database_systems** which must have attribute "name" value equal to SYS1, SYS2 and SYS3. I'll try it and will update you with the results. I also need to insert this SYS3 into another record which have the same ID. Don't confuse **ID** with ACCT_ID. So this problem occurs due to my code that runs on hundreds of records and pick the wrong record to add this SYS3. Well I'll try this and definitely write something for the next task. Thanks a lot for help. – Superman Feb 25 '14 at 15:18
  • Yes I think you should open a new question about the insertion as you did not ask that in the initial question. I believe I have answered what you asked. Obviously I cannot answer what you did not ask... as I am not clairvoyant. – adamretter Feb 25 '14 at 16:14
  • SQL Error [10507]: An expression of data type "( item(), item()+ )" cannot be used when the data type "item()" is expected in the context. Error QName=err:XPTY0004.. SQLCODE=-16003, SQLSTATE=10507, DRIVER=4.16.53 – Superman Feb 26 '14 at 07:57
  • You might try replacing the `eq` with `=`. – adamretter Feb 28 '14 at 11:53
  • Hmm you might have to get someone who knows the limitations of DB2's XQuery support involved. I think the XQuery looks fine, I just don't have access to DB2 to test it any further, sorry. – adamretter Feb 28 '14 at 12:56