Openxml question

[transparent]

Hello.
I have a question regarding inputing data into a database from xml documents using openxml.

I have the following xml.

<incidents>

<incident id=&quot;10&quot; name&quot;crashed car&quot;>
<reporter id=&quot;2&quot; name=&quot;ted&quot;/>
</incident>

<incident id=&quot;11&quot; name&quot;crashed bike&quot;>
<reporter id=&quot;1&quot; name=&quot;rick&quot;/>
</incident>

<incident id=&quot;12&quot; name&quot;crashed spaceship&quot;>
<reporter id=&quot;3&quot; name=&quot;paul&quot;/>
</incident>

</incidents>

the tables involved are an incidents table, and a reporter table.

incident table has columns incidentid (PK), name
reporter table has columns reporterid(PK), incidentid(FK), name


Now to insert all the incidents
I would use

openxml(@itree,&quot;/incidents/incident&quot

however with all the reporteres I need to extract the associated incidentID. How do I do this? Will

openxml(@itree,&quot;/incidents/incident/reporter&quot

do it?

 

[wepisan]

Hi,

Also try adding incident_id to the reporter element as it will help you associate a reporter to incident.

Here's the sample:

declare @XmlHandle int

declare @incident table (
inc_id int,
inc_name varchar(25)
)

declare @report table (
inc_id int,
rep_id int,
rep_name varchar(25)
)

EXEC sp_xml_preparedocument @XmlHandle output,
'<incidents>
<incident id=&quot;10&quot; name=&quot;crashed car&quot;>
<reporter id=&quot;2&quot; inc_id=&quot;10&quot; name=&quot;ted&quot; />
</incident>
<incident id=&quot;11&quot; name=&quot;crashed bike&quot;>
<reporter id=&quot;1&quot; inc_id=&quot;11&quot; name=&quot;rick&quot; />
</incident>
<incident id=&quot;12&quot; name=&quot;crashed spaceship&quot;>
<reporter id=&quot;3&quot; inc_id=&quot;12&quot; name=&quot;paul&quot; />
</incident>
</incidents>'

insert into @incident
SELECT *
FROM OPENXML (@XmlHandle, '/incidents/incident',1)
WITH (
inc_id int '@id',
inc_name varchar(25) '@name'
)

select * from @incident

insert into @report
SELECT *
FROM OPENXML (@XmlHandle, '/incidents/incident/reporter',1)
WITH (
inc_id int '@id',
rep_id int '@inc_id',
rep_name varchar(25) '@name'
)

select * from @report


EXEC sp_xml_removedocument @XmlHandle


Hope this helps,
Vaiyapuri Subramanian