soozh
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:
I can extract and save the content of each <action> node using the following code:
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?
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>' ;
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.