Extract out Parent/child records from XML String

  • Hi there

    I  have an XML String from which I would like to extract the data out to create a set of records

    My xml is defined as follows:

    declare @DataSheetXML xml = '<Datasheet










































    Now in the above example, i want to create records in a master table (ProbeDefinition)

    So in the above example, you would have 5 records created in the ProbeDefinition table as follows:



    Columns for this table are [ID] which is a Identity column and [ProbeDefinition]

    Then for each of the ProbeDefinitions, there is a set of entries referred to as the


    What I would like to do , is to create sub records for this , which are related to


    So you would have the following:



    I can use the following

    select @DataSheetXML.value('count(Datasheet/ProbeList/ProbeDefinition)', 'int')

    Which will give me the number of master records to insert which is 5

    But im stuck on how to extract the channel list entries for each of the probe definitions.

    Whats the easiest way that I can extract the master and child records from this XML String?







  • See if this helps

    select pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS MasterRecordNumber,
    cl.b.value('.','int') as ChannelListValue
    from @DataSheetXML.nodes('Datasheet/ProbeList/ProbeDefinition') AS pd(a)
    cross apply pd.a.nodes('ChannelList/int') cl(b);


    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

  • Hi Mark

    That works perfectly for me. Thank you very much

  • This was removed by the editor as SPAM

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

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