Post reply

Extracting out elements from XML String based on condition in tag

  • 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.

  • 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

  • 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

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply