How to filter out unwanted nodes?

I would like to retrieve the values of ProductIDs nodes but the condition is to ignore any ProductIDs that is a child of <AlternativeState> node.

Please keep in mind that xml data comes from table and the value of ProductIDs could appear anywhere else as well so I am afraid using exact XPath is not a choice. The only time when ProductIDs is not returned when its a child of AlternativeState.

Currently I have something like this but it returns all of the productIDs from the xml instance

SELECT UsageID, xmlState.query('

for $productIDs in //*/ProductIDs

order by $productIDs

return string($productIDs)') AS ProductIDs

FROM Usage.UsageAnalysis

WHERE UsageID = 91923

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<ProductIDs>46420</ProductIDs>

<AlternativeState><!-- Ignore anything in this element-->

<OtherEvents>

<ProductIDs>46420</ProductIDs>

</OtherEvents>

<MarketSize>

<ProductIDs>46431,46440,46469,46470</ProductIDs>

</MarketSize>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<ProductIDs>2128,2459,46420,46440,46470</ProductIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

In your sample XML the AlternativeState element does not have a ProductIDs child, only a grand child. So for that XML sample the expression

Code Snippet

for $productIDs in //*[not(self::AlternativeState)]/*/ProductIDs

order by $productIDs

return string($productIDs)

should filter out what you want to filter out. Using an ancestor axis test should solve the problem in general but that axis is not currently supported in XQuery for SQL Server 2005.

How to filter out unwanted data

I have the following fields in table A:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 41142 | 1,800.00
20/02/2006 | 41142 | 2,700.00
25/02/2006 | 740245 | 5,200.00
I have the following fields in table B:
Date | Descrip | Amt Dr
--
02/02/2006 |88258 | 1,400.00
17/02/2006 |740244 | (1,500.00)
25/02/2006 |740245 | 5,200.00
There are no referencial key between TableA & TableB,
What i want is to extract the date,descrip & Amt data from
Table A where it's Descrip data is not the same as the data
in Table B's Descrip column.
My sql syntax is as follows:
SELECT
CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
[Add Back]
FROM TableA,TableB
WHERE TableA.Descrip <> TableB.Descrip
GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
The output of the above is as follows:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 0 | 0
20/02/2006 | 0 | 0
25/02/2006 | 740245 | 5,200.00
Notice that, descrip with 740245 appearing in both tables
is what the sql should filter out, but failed to do so,
what i want is as below:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
Can i achieve this? Please help.
*** Sent via Developersdex http://www.examnotes.net ***Dave
Untested
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
"Dave dcartford" <dcartford@.gmail.com> wrote in message
news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
> I have the following fields in table A:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 41142 | 1,800.00
> 20/02/2006 | 41142 | 2,700.00
> 25/02/2006 | 740245 | 5,200.00
> I have the following fields in table B:
> Date | Descrip | Amt Dr
> --
> 02/02/2006 |88258 | 1,400.00
> 17/02/2006 |740244 | (1,500.00)
> 25/02/2006 |740245 | 5,200.00
> There are no referencial key between TableA & TableB,
> What i want is to extract the date,descrip & Amt data from
> Table A where it's Descrip data is not the same as the data
> in Table B's Descrip column.
> My sql syntax is as follows:
>
> SELECT
> CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
> [Add Back]
> FROM TableA,TableB
> WHERE TableA.Descrip <> TableB.Descrip
> GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
> The output of the above is as follows:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 0 | 0
> 20/02/2006 | 0 | 0
> 25/02/2006 | 740245 | 5,200.00
> Notice that, descrip with 740245 appearing in both tables
> is what the sql should filter out, but failed to do so,
> what i want is as below:
>
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
>
> Can i achieve this? Please help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Is someone else posting from Uri's name.. juz joking :)
try this
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
"Uri Dimant" wrote:

> Dave
> Untested
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
>
> "Dave dcartford" <dcartford@.gmail.com> wrote in message
> news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
>|||>> > Table A where it's Descrip data is not the same as the data
Just change NOT EXISTS to EXISTS
create table #table_a (c1 datetime,descr varchar(10))
create table #table_b (c1 datetime,descr varchar(10))
insert #table_a values ('20060101','a')
insert #table_a values ('20060101','h')
insert #table_a values ('20060101','r')
insert #table_b values ('20060101','a')
insert #table_b values ('20060101','c')
select * from #table_a where exists
(select * from #table_b where #table_a.descr=#table_b.descr)
drop table #table_a,#table_b
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:3F740FCC-A686-49F9-BF5C-7544C822E32D@.microsoft.com...
> Is someone else posting from Uri's name.. juz joking :)
> try this
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
> "Uri Dimant" wrote:
>|||Uri,
Shouldn't the query be like this' I apologise if I irritated you.
select * from #table_a where not exists
(select * from #table_b where #table_a.descr=#table_b.descr)|||Hi
Doh, I need a cofee , my mistake. I don't why ,i have been assumed that he
needs the same descr to get out
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6A886FB3-F225-4863-A453-C0E6C07F1819@.microsoft.com...
> Uri,
> Shouldn't the query be like this' I apologise if I irritated you.
> select * from #table_a where not exists
> (select * from #table_b where #table_a.descr=#table_b.descr)
>