XQuery: filter on optional attributes
-
declare @xml XML(ParetoSchema)
set @xml='<?xml version="1.0" encoding="utf-8"?>
<ParetoDefinition xmlns="http://Equazion.Reporting.Model/Paretos.xsd">
<Paretos>
<Pareto id="0" costObject="CustomerShipTo" abcGrouping="Country" attributeType="ProfitLabel" attributeName="Revenue" operation="Update">
<HighestParetos>
<HighestPareto id="0" costObject="CustomerBillTo" classGroupingId="36" operation="Create"/>
<HighestPareto id="0" costObject="CustomerSoldTo" classGroupingId="40" operation="None"/>
<HighestPareto id="0" costObject="CustomerSoldTo" classGroupingId="42" operation="None"/>
</HighestParetos>
</Pareto>
<Pareto id="0" costObject="CustomerSoldTo" classGroupingId="36" attributeType="ProfitElementGroup" attributeName="Rebates" operation="Create">
<HighestParetos>
<HighestPareto id="0" costObject="CustomerBillTo" classGroupingId="36" operation="Create"/>
<HighestPareto id="0" costObject="CustomerBillTo" classGroupingId="40" operation="Create"/>
</HighestParetos>
</Pareto>
<Pareto id="0" costObject="CustomerBillTo" attributeType="Dimension" attributeName="RevenueUnits" operation="None">
<HighestParetos/>
</Pareto>
<Pareto id="0" costObject="SalesItem" classGroupingId="36" abcGrouping="Beer" attributeType="ProfitLabel" attributeName="Revenue" operation="None">
<HighestParetos/>
</Pareto>
</Paretos>
</ParetoDefinition>'
--I want to filter on the first Pareto node which hasn't got a abcgrouping attribute.
--How do I say is null?
SELECTDENSE_RANK() over (order by tref) Pareto,
tref.value('@costObject','sysname') [CostObject],
tref.value('@abcGrouping','sysname') [abcGrouping],
tref.value('@classGroupingId','sysname')[classGroupingId],
tref.value('@attributeType','sysname')[attributeType],
tref.value('@attributeName','sysname')[attributeName],
tref.value('@operation','sysname') [Operation]
FROM @xml.nodes('declare namespace d="http://Equazion.Reporting.Model/Paretos.xsd";
/d:ParetoDefinition/d:Paretos/d:Pareto[@costObject="CustomerShipTo"][@abcGrouping="Country"]
[@abcGrouping=???][@attributeType="ProfitLabel"][@attributeName="Revenue"]') as T(tref)
-
SELECT DENSE_RANK() over (order by tref) Pareto,
tref.value('@costObject','sysname') [CostObject],
tref.value('@abcGrouping','sysname') [abcGrouping],
tref.value('@classGroupingId','sysname') [classGroupingId],
tref.value('@attributeType','sysname') [attributeType],
tref.value('@attributeName','sysname') [attributeName],
tref.value('@operation','sysname') [Operation]
FROM @xml.nodes('declare namespace d="http://Equazion.Reporting.Model/Paretos.xsd";
/d:ParetoDefinition/d:Paretos/d:Pareto
[not (@abcGrouping)]') as T(tref)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply