Extracting out elements from XML String based on condition in tag

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  December 6, 2021 at 6:56 pm

  #3960959

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

  ---

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  December 6, 2021 at 8:26 pm

  #3960976

  Hey Phil

  Ok these are attached

  Attachments:

  You must be logged in to view attached files.

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  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);

  ---

• Weegee71

  SSChasing Mays

  Points: 607

  More actions

  December 7, 2021 at 11:43 am

  #3961245

  Hi Phil

   

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