| Author |
xquery and join in sql 2005
|
|
| jcollum@gmail.com 2006-10-25, 6:06 am |
| Hi, I have this code that performs an xquery on a xml column in the
db.
Select Table1.XmlVal.query('
for $persons in //person
for $apply in //node2
where
$persons/allowedItems/item/itemKey = $apply/item/itemKey
and
$apply/item/buyIt = "true"
and
$persons/allowedItems/item/canBuyIt = "True"
return
<person>
{ $persons/personId }
{ $persons/allowedItems/item }
{ $apply/item/itemKey }
</person>
') AS Result
FROM Items
WHERE PoRef = '522'
Some of the table/element names have been changed to protect innocent
IP. So maybe it won't work exactly but the basics are the same.
The issue that's coming up is that I'm getting values in my output that
don't match the $persons/allowedItems/item/canBuyIt = "True" criteria.
I don't see why that would happen. Help please!
| |
| Kent Tegels 2006-10-25, 6:06 am |
| Hello jcollum@gmail.com,
> The issue that's coming up is that I'm getting values in my output
> that don't match the $persons/allowedItems/item/canBuyIt = "True"
> criteria. I don't see why that would happen. Help please!
You need to remember that although for looks like element-by-element enumeration,
it's not, it's working like any other declarative query on a set of elements.
So the question here is "do any of the documents you think are being inappropriately
selected have *any* path that have canBuyIt = True in scope? If so, that's
why.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
| |
| JCollum 2006-10-25, 6:06 am |
| Well, a lot of the elements have canBuyIt = True. But not all of them
should be in my results. I need to join the two "tables" on their
itemId but only where the canBuyIt value is "True".
All of the elements are in the same document. Some of the items have
canBuyIt = "True" and some don't.
You lost me with "working like any other declarative query on a set of
elements". As in XPath? Dunno.
-- J
Kent Tegels wrote:
> Hello jcollum@gmail.com,
>
>
> You need to remember that although for looks like element-by-element enumeration,
> it's not, it's working like any other declarative query on a set of elements.
> So the question here is "do any of the documents you think are being inappropriately
> selected have *any* path that have canBuyIt = True in scope? If so, that's
> why.
>
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
| |
| Kent Tegels 2006-10-25, 6:06 am |
| Hello JCollum,
As in T-SQL. Let's try this: post a three or four data elements so we can
see the data and I'll to help you understand this.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
| |
| Jinghao Liu 2006-10-25, 6:06 am |
| You query looks ok from syntax and semantics. Without see what data it
against and the result, I don't know what could be wrong. Can you make a
similar repro case without sensitive data?
<jcollum@gmail.com> wrote in message
news:1161551671.141401.201290@i3g2000cwc.googlegroups.com...
> Hi, I have this code that performs an xquery on a xml column in the
> db.
>
> Select Table1.XmlVal.query('
> for $persons in //person
> for $apply in //node2
>
> where
> $persons/allowedItems/item/itemKey = $apply/item/itemKey
> and
> $apply/item/buyIt = "true"
> and
> $persons/allowedItems/item/canBuyIt = "True"
>
> return
> <person>
> { $persons/personId }
> { $persons/allowedItems/item }
> { $apply/item/itemKey }
> </person>
> ') AS Result
> FROM Items
> WHERE PoRef = '522'
>
> Some of the table/element names have been changed to protect innocent
> IP. So maybe it won't work exactly but the basics are the same.
>
> The issue that's coming up is that I'm getting values in my output that
> don't match the $persons/allowedItems/item/canBuyIt = "True" criteria.
> I don't see why that would happen. Help please!
>
| |
| JCollum 2006-10-25, 6:06 am |
| Here's the data sample:
<root>
<person>
<personId>0</personId>
<matchItems>
<item>
<itemKey>DERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>True</canBuy>
</item>
<item>
<itemKey>BERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>False</canBuy>
</item>
</matchItems>
</person>
<buyItems>
<item>
<itemKey>BERTH</itemKey>
<doBuy>true</doBuy>
</item>
<item>
<itemKey>DERTH</itemKey>
<doBuy>true</doBuy>
</item>
</buyItems>
</root>
So in this case what I need to do is select all of the items from the
matchItems node where canBuy='True' and join that with
//buyItems/item/doBuy='true' on the programKey. A straight up inner
join on criteria. In this case I should only get one item key back:
DERTH. In the sample query that I posted I'd get all of the items back.
My plan B on this is to load the data in to two CTE's and filter it
that way. But doing it in one expression seems like it should be
possible.
Thanks.
-- J
Jinghao Liu wrote:
> You query looks ok from syntax and semantics. Without see what data it
> against and the result, I don't know what could be wrong. Can you make a
> similar repro case without sensitive data?
>
| |
| markc600@hotmail.com 2006-10-25, 6:06 am |
|
I think this is what you want
declare @x xml
set @x='
<root>
<person>
<personId>0</personId>
<matchItems>
<item>
<itemKey>DERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>True</canBuy>
</item>
<item>
<itemKey>BERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>False</canBuy>
</item>
</matchItems>
</person>
<buyItems>
<item>
<itemKey>BERTH</itemKey>
<doBuy>true</doBuy>
</item>
<item>
<itemKey>DERTH</itemKey>
<doBuy>true</doBuy>
</item>
</buyItems>
</root>
'
Select @x.query('
for $persons in //person/matchItems/item/canBuy[text()= "True"]
for $apply in //buyItems/item/doBuy[text() = "true"]
where $persons/../itemKey = $apply/../itemKey
return
<person>
{ $persons/../../../personId }
{ $persons/.. }
{ $apply/../itemKey }
</person>
') AS Result
| |
| Jinghao Liu 2006-10-25, 6:06 am |
| Here is the correct way to do what you want:
declare @x xml
set @x=
'<root>
<person>
<personId>0</personId>
<matchItems>
<item>
<itemKey>DERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>True</canBuy>
</item>
<item>
<itemKey>BERTH</itemKey>
<doPurge>False</doPurge>
<canBuy>False</canBuy>
</item>
</matchItems>
</person>
<buyItems>
<item>
<itemKey>BERTH</itemKey>
<doBuy>true</doBuy>
</item>
<item>
<itemKey>DERTH</itemKey>
<doBuy>true</doBuy>
</item>
</buyItems>
</root>'
select @x.query('
for $personItem in /root/person/matchItems/item
for $buyItem in /root/buyItems/item
where
$personItem/itemKey = $buyItem/itemKey
and
$buyItem/doBuy = "true"
and
$personItem/canBuy = "True"
return
<person>
{ $personItem/../../personId }
{ $personItem }
{ $buyItem/itemKey }
</person>
')
The semantics of your previous query (below) is
1). select all persons who HAVE items satisfy the predicates (for $persons
in //person)
2). and then return ALL items under this person ( {
$persons/allowedItems/item } )
That's why you see items not satisfy ( $persons/allowedItems/item/canBuyIt =
"True")
Select Table1.XmlVal.query('
for $persons in //person
for $apply in //node2
where
$persons/allowedItems/item/itemKey = $apply/item/itemKey
and
$apply/item/buyIt = "true"
and
$persons/allowedItems/item/canBuyIt = "True"
return
<person>
{ $persons/personId }
{ $persons/allowedItems/item }
{ $apply/item/itemKey }
</person>
') AS Result
"JCollum" <jcollum@gmail.com> wrote in message
news:1161641820.943551.264070@h48g2000cwc.googlegroups.com...
> Here's the data sample:
> <root>
> <person>
> <personId>0</personId>
> <matchItems>
> <item>
> <itemKey>DERTH</itemKey>
> <doPurge>False</doPurge>
> <canBuy>True</canBuy>
> </item>
> <item>
> <itemKey>BERTH</itemKey>
> <doPurge>False</doPurge>
> <canBuy>False</canBuy>
> </item>
> </matchItems>
> </person>
> <buyItems>
> <item>
> <itemKey>BERTH</itemKey>
> <doBuy>true</doBuy>
> </item>
> <item>
> <itemKey>DERTH</itemKey>
> <doBuy>true</doBuy>
> </item>
> </buyItems>
> </root>
>
> So in this case what I need to do is select all of the items from the
> matchItems node where canBuy='True' and join that with
> //buyItems/item/doBuy='true' on the programKey. A straight up inner
> join on criteria. In this case I should only get one item key back:
> DERTH. In the sample query that I posted I'd get all of the items back.
>
>
> My plan B on this is to load the data in to two CTE's and filter it
> that way. But doing it in one expression seems like it should be
> possible.
> Thanks.
> -- J
>
> Jinghao Liu wrote:
>
| |
| JCollum 2006-10-25, 6:06 am |
| Thanks guys, that totally did it. I'm still working out the exact
differences between what I originally had, but both of your solutions
produced the correct results.
On Oct 24, 11:16 am, "Jinghao Liu" <jingh...@microsoft.com> wrote:[color=darkred
]
> Here is the correct way to do what you want:
>
> declare @x xml
> set @x=
> '<root>
> <person>
> <personId>0</personId>
> <matchItems>
> <item>
> <itemKey>DERTH</itemKey>
> <doPurge>False</doPurge>
> <canBuy>True</canBuy>
> </item>
> <item>
> <itemKey>BERTH</itemKey>
> <doPurge>False</doPurge>
> <canBuy>False</canBuy>
> </item>
> </matchItems>
> </person>
> <buyItems>
> <item>
> <itemKey>BERTH</itemKey>
> <doBuy>true</doBuy>
> </item>
> <item>
> <itemKey>DERTH</itemKey>
> <doBuy>true</doBuy>
> </item>
> </buyItems>
> </root>'
> select @x.query('
> for $personItem in /root/person/matchItems/item
> for $buyItem in /root/buyItems/item
> where
> $personItem/itemKey = $buyItem/itemKey
> and
> $buyItem/doBuy = "true"
> and
> $personItem/canBuy = "True"
>
> return
> <person>
> { $personItem/../../personId }
> { $personItem }
> { $buyItem/itemKey }
> </person>
> ')
>
> The semantics of your previous query (below) is
> 1). select all persons who HAVE items satisfy the predicates (for $persons
> in //person)
> 2). and then return ALL items under this person ( {
> $persons/allowedItems/item } )
>
> That's why you see items not satisfy ( $persons/allowedItems/item/canBuyIt =
> "True")
>
> Select Table1.XmlVal.query('
> for $persons in //person
> for $apply in //node2
>
> where
> $persons/allowedItems/item/itemKey = $apply/item/itemKey
> and
> $apply/item/buyIt = "true"
> and
> $persons/allowedItems/item/canBuyIt = "True"
>
> return
> <person>
> { $persons/personId }
> { $persons/allowedItems/item }
> { $apply/item/itemKey }
> </person>
> ') AS Result
>
> "JCollum" <jcol...@gmail.com> wrote in messagenews:11616418
20.943551.264070@h48g2000cwc.googlegroups.com...
>
>
>
>
>
>
|
|
|
|