Topic
  • 1 reply
  • Latest Post - ‏2012-03-20T06:00:52Z by MatthiasNicola
SystemAdmin
SystemAdmin
230 Posts

Pinned topic xmlexists on multiple xml columns in join query

‏2012-03-19T21:05:18Z |
Hi,

We have the following query:
select * from data_table where
xmlexists('$c /document1/element[child/@valuehttp://. >= $val1] passing xmlcol1 as "c", cast('122' as varchar(512) as "val1")
and
xmlexists('$c /document2/element[child/@valuehttp://. >= $val1] passing xmlcol2 as "c", cast('4223' as varchar(512) as "val1")

Running the below query seperately (with only first condition) returns 21 records
select * from data_table where
xmlexists('$c /document1/element[child/@valuehttp://. >= $val1] passing xmlcol1 as "c", cast('122' as varchar(512) as "val1")

And running the below independently (with only the second condition) returns 18 records.
select * from data_table where
xmlexists('$c /document2/element[child/@valuehttp://. >= $val1] passing xmlcol2 as "c", cast('4223' as varchar(512) as "val1")

But there is only one record which satisfies both the conditions, so we expect the number of records returned to be just one. But the join query with the two xmlexists returns 21 records instead (and the returned results are same as if the second xmlexists condition does not exist). Could someone please let me know if I am missing something?

Advance Thanks,
Vijay.
Updated on 2012-03-20T06:00:52Z at 2012-03-20T06:00:52Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    322 Posts

    Re: xmlexists on multiple xml columns in join query

    ‏2012-03-20T06:00:52Z  
    Hi Vijay,

    I suspect the forum has modified the text of your queries, because the path child/@valuehttp://. looks somewhat strange. For code and query samples, this forum works best if you use code tags to separate your query from other text. You would type it like this, but without the spaces before and after the tag name "code":

    { code }
    your query here
    { code }

    Another option is that you attach a text file to your next post.

    Now to your actual question. The behavior that you are describing certainly sounds strange. Can you show the one record that should be returned and another one that is also returned but shouldn't be returned? I'm hoping that this will allows to get to the bottom of it.

    Thanks,

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/