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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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