Post reply

Extracting out elements from XML String based on condition in tag

  • December 6, 2021 at 5:29 pm

    #3960940

    Hi there

    I am trying to the Channel list from a particular XML file  in lines 868 - 872 (Instrumental Channel List)

    Now i tried the following

    DECLARE @DataSheetXML XML

    -- Extract Datasheet, so that Probe Definition and Channel List can be extractted

    SELECT @DataSheetXML = DataSheetXML from [#DeviceMasterList]

    SELECT

    pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS [MPRowID],

    NEWID() AS [MeasuringPointEntryChannelID],

    cl.b.value('.','int') AS [ChannelID]

    FROM

    @DataSheetXML.nodes('Datasheet/InstrumentationChannelList') AS pd(a)

    CROSS APPLY pd.a.nodes('InstrumentationChannelList/int') cl(b)

    #

     

    But couldnt extract the ChannelList

    What am I doing wrong?

    Attached are the following:

     

    1. Create #DeviceMasterList (scripts out the table)
    2. Test.xml  (Whole XML for a single devic
    3.  Screenshot of expected output (ChannelList.png)

     

     

    TIA

     

     

    Attachments:
    You must be logged in to view attached files.

  • December 6, 2021 at 6:56 pm

    #3960959

    Please try renaming your XML file as .TXT and attaching again. Some file types are blocked.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 6, 2021 at 8:26 pm

    #3960976

    Hey Phil

    Ok these are attached

    Attachments:
    You must be logged in to view attached files.

  • December 7, 2021 at 10:08 am

    #3961217

    I created a temp table and inserted your XML doc into it. The following code worked for me

    SELECT ChannelId = v.n1.value('(.)[1]', 'INT')
    FROM #SomeXML sx
        CROSS APPLY sx.X.nodes('Datasheet/InstrumentationChannelList/int') v(n1);

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 7, 2021 at 11:43 am

    #3961245

    Hi Phil

     

    Brilliant. That works very well. Thank you very much for your help

Viewing 5 posts - 1 through 4 (of 4 total)

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