XQuery: filter on optional attributes

• wim.buyens

  Ten Centuries

  Points: 1159

  More actions

  September 28, 2010 at 3:49 am

  #224440

  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)

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  September 28, 2010 at 5:24 am

  #1228261

  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