Extracting out elements from XML String based on condition in tag

• Weegee71

  SSChasing Mays

  Points: 604

  More actions

  July 20, 2021 at 12:15 pm

  #3912019

  Hi there

  i want to extract data from XML string based on certain criteria

  My xml is as follows:

  declare @DataSheetXML xml = '<Datasheet

  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <Attributes>

  <InterfaceDefinition>

  <id>RadioStats_00</id>

  <bits>16</bits>

  <fixed_bits>0</fixed_bits>

  <signed>false</signed>

  <total_bits>0</total_bits>

  </InterfaceDefinition>

  </Attributes>

  <ChannelList>

  <ChannelDefinition IsAlarmable="True" IsEnableable="True">

  <Name>th</Name>

  <Index>11</Index>

  <InterfaceID>ad779x_5</InterfaceID>

  <interfaceReference>

  <id>ad779x_5</id>

  <bits>16</bits>

  <fixed_bits>0</fixed_bits>

  <fnName>sixteen_bit_raw</fnName>

  <signed>false</signed>

  <total_bits>0</total_bits>

  </interfaceReference>

  <Description>Temperature</Description>

  <Group>instrumentation</Group>

  <fnName>decode_thermistor_V1</fnName>

  <Dimension>temperature</Dimension>

  <LoggerUnits>Degrees Celsius</LoggerUnits>

  <Upper>85</Upper>

  <Lower>-25</Lower>

  <Noise xsi:nil="true" />

  <Uncertainty>0.1</Uncertainty>

  <DisplayName>Temperature</DisplayName>

  <IsNormal>true</IsNormal>

  <AlarmRangePropertyId>0</AlarmRangePropertyId>

  <adjustment_property_id>280</adjustment_property_id>

  <SensorScaling>

  <OutDimension>temperature</OutDimension>

  <OutUnits>Degrees Celsius</OutUnits>

  <OutSymbol>°C</OutSymbol>

  <Coeff>

  <double>0</double>

  <double>1</double>

  </Coeff>

  </SensorScaling>

  <Channel_Links>

  <int>17</int>

  </Channel_Links>

  <HasMinMax>false</HasMinMax>

  <UsesInstrumentationInterval>false</UsesInstrumentationInterval>

  <UsesAmbientInterval>false</UsesAmbientInterval>

  <HasDescription>false</HasDescription>

  <HasAlarm>false</HasAlarm>

  <HasAdjustment>false</HasAdjustment>

  <ProbeIndex>0</ProbeIndex>

  <SourceList />

  </ChannelDefinition>

  <ChannelDefinition IsAlarmable="True" IsEnableable="True">

  <Name>rh</Name>

  <Index>14</Index>

  <InterfaceID>sht3x_rh_1</InterfaceID>

  <interfaceReference>

  <id>sht3x_rh_1</id>

  <bits>16</bits>

  <fixed_bits>0</fixed_bits>

  <fnName>sixteen_bit_x100</fnName>

  <signed>false</signed>

  <total_bits>0</total_bits>

  </interfaceReference>

  <Description>Humidity</Description>

  <Group>instrumentation</Group>

  <fnName>decode_sht3x_rh</fnName>

  <Dimension>humidity</Dimension>

  <LoggerUnits>%RH</LoggerUnits>

  <Upper>110</Upper>

  <Lower>-10</Lower>

  <Noise xsi:nil="true" />

  <Uncertainty>0.5</Uncertainty>

  <DisplayName>Humidity</DisplayName>

  <IsNormal>true</IsNormal>

  <AlarmRangePropertyId>0</AlarmRangePropertyId>

  <adjustment_property_id>281</adjustment_property_id>

  <SensorScaling>

  <OutDimension>humidity</OutDimension>

  <OutUnits>%RH</OutUnits>

  <OutSymbol>%RH</OutSymbol>

  <Coeff>

  <double>0</double>

  <double>1</double>

  </Coeff>

  </SensorScaling>

  <Channel_Links>

  <int>17</int>

  </Channel_Links>

  <HasMinMax>false</HasMinMax>

  <UsesInstrumentationInterval>false</UsesInstrumentationInterval>

  <UsesAmbientInterval>false</UsesAmbientInterval>

  <HasDescription>false</HasDescription>

  <HasAlarm>false</HasAlarm>

  <HasAdjustment>false</HasAdjustment>

  <ProbeIndex>0</ProbeIndex>

  <SourceList />

  </ChannelDefinition>

  </ChannelList>

  </Datasheet>'

  select @DataSheetXML

  Now from the above I want to be able to extract with the criteria IsEnableable="True"

  How do I do this?

  Secondly I would like to extract the following information for each channel :

  • Index
  • OutUnits

  see attached screenshot

  So i would then have the following information:

   

  

  Now I can get to the ChannelID by doing the following:

  select @DataSheetXML.value('(Datasheet/ChannelList/ChannelDefinition/Index)[1]', 'int' ) --2.01

  select @DataSheetXML.value('(Datasheet/ChannelList/ChannelDefinition/Index)[2]', 'int' ) --2.01

  select @DataSheetXML.value('(Datasheet/ChannelList/ChannelDefinition/SensorScaling/OutUnits)[1]', 'varchar(20)' ) --2.01

  But I can get in the table format shown above

  How can I do this please?

  Attachments:

  You must be logged in to view attached files.

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 20, 2021 at 2:50 pm

  #3912086

  This should work for you

  select x.r.value('Index[1]','int') as ChannelID,
         x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') as [SI Units]
  from @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r);

  ____________________________________________________

  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

• Weegee71

  SSChasing Mays

  Points: 604

  More actions

  July 20, 2021 at 3:49 pm

  #3912110

  Hi Mark

  Briiliant that works very well. Thank you very much for doing that.

  I can see what you are doing

  @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r) --> Subselects the group to query on

  Then x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') will extract the OutUnits

  Ive tweaked it also to return the OutDimension and OutSymbol in there

  x.r.value('(SensorScaling/OutDimension)[1]','varchar(20)') as [OutDimension],

  x.r.value('(SensorScaling/OutSymbol)[1]','varchar(20)') as [OutSymbol]

  I see that you have to use [1]  as there is only 1 value in this entry