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

    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>

    <ProbeList>

    <ProbeDefinition>

    <ChannelList>

    <int>17</int>

    <int>11</int>

    <int>14</int>

    </ChannelList>

    </ProbeDefinition>

    <ProbeDefinition>

    <ChannelList>

    <int>12</int>

    </ChannelList>

    </ProbeDefinition>

    <ProbeDefinition>

    <ChannelList>

    <int>13</int>

    </ChannelList>

    </ProbeDefinition>

    <ProbeDefinition>

    <ChannelList>

    <int>15</int>

    </ChannelList>

    </ProbeDefinition>

    <ProbeDefinition>

    <ChannelList>

    <int>16</int>

    </ChannelList>

    </ProbeDefinition>

    </ProbeList>

    </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:

    ProbeDefintion

    ProbeDefintion_Master

    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

    ChannelList

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

    ProbeDefintions

    So you would have the following:

    ProbeDefinitionChannelList

     

    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

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • 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