Link to home
Start Free TrialLog in
Avatar of soozh
soozhFlag for Sweden

asked on

Parse and insert xml with master detail relationship

HEllo,

I have an xml wihich i am try to extract data from.  It looks like this:

declare @xmlData xml = '
<BPSD-registret><Measurement2018>
<naza_bpsd><CarePlan>Hi</CarePlan>'+
  '<Actions>'+
    '<Current>'+
      '<Action>'+
        '<id>124</id>'+
        '<msr_id>405397</msr_id>'+
        '<activity>qdq</activity>'+
        '<when>d</when>'+
        '<where>qdw</where>'+
        '<how>dsf</how>'+
        '<whom>sdfsdf</whom>'+
        '<duration>sdfsdf</duration>'+
        '<estimations>'+
        '<estimation>2</estimation>'+
        '</estimations>'+
        '<measures/>'+
      '</Action>'+
      '<Action>'+
        '<id>125</id>'+
        '<msr_id>405397</msr_id>'+
        '<activity>Aaaa</activity>'+
        '<when>jklj</when>'+
        '<where>lk</where>'+
        '<how>jlk</how>'+
        '<whom>j</whom>'+
        '<duration>lkj</duration>'+
        '<estimations>'+
          '<estimation>1</estimation>'+
        '</estimations>'+
        '<measures>'+
          '<measure>1</measure>'+
          '<measure>2</measure>'+
          '<measure>3</measure>'+
        '</measures>'+
      '</Action>'+
    '</Current>'+
    '<Previous/>'+
  '</Actions>'+
'</naza_bpsd>'+
'</Measurement2018></BPSD-registret>' ;

Open in new window


I can extract and save the content of each <action> node using the following code:

insert into BPSD_MeasurementActions
select
a.value('(activity)[1]', 'nvarchar(500)' ) as act_Activity,
a.value('(when)[1]', 'nvarchar(500)' ) as act_When,
a.value('(where)[1]', 'nvarchar(500)' ) as act_Where,
a.value('(how)[1]', 'nvarchar(500)' ) as act_How,
a.value('(whom)[1]', 'nvarchar(500)' ) as act_With,
a.value('(duration)[1]', 'nvarchar(500)' ) as act_HowLong
from @xmlData.nodes('/BPSD-registret/Measurement2018/naza_bpsd/Actions/Current/Action') Actions(a)

Open in new window


The table BPSD_MeasurementActions has an identity column called Id (Integer).  

The problem is that the node <measures> has now been added to the xml.  I need to save the values of each <measure> node.   Therefore there is a one to many relationship with the <action> node and i need to save these values in a details table.  The value of Id from BPSD_MeasurementActions will be the foreign key.

Any sugestions on how i can traverse this xml and do the appropriate insertions?
ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial