Extract out Parent/child records from XML String

Question

Extract out Parent/child records from XML String

Weegee71

SSChasing Mays

Points: 604
More actions
July 9, 2021 at 2:07 pm

#3906107

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?
July 12, 2021 at 10:46 am

This was removed by the editor as SPAM

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

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Hi Mark

That works perfectly for me. Thank you very much