Problem Joining Parent and Children

I'm having problems joining the parent and children together from an
xml document using openxml.
The below example returns two result sets that I want bring together
using a left join.
The problem is that the children due not have an id associated with
them, so there is no key to perform the join. I'm trying to perform
the join based on the metaproperties @.mp:id and @.mp:parent, but it is
not quite working.
I can't see a solution without creating a cursor to step through the
parent rows.
The XML Document is a little different than what I like to work with,
but unfortunately it can not be changed. The child records are in the
sec_call_result_cd nodes and there may or may not be children.
Any help appreciated.
Thanks
Bob Horkay
declare @.doc varchar(8000)
declare @.hdoc integer
set @.doc =
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<ecm_data>
<calling_lists>
<calling_list>
<calling_list_id>44</calling_list_id>
<list_nm>GLO Calling</list_nm>
<list_status_cd>1</list_status_cd>
</calling_list>
<calling_list>
<calling_list_id>45</calling_list_id>
<list_nm>GLO Vendor Calling</list_nm>
<list_status_cd>0</list_status_cd>
</calling_list>
</calling_lists>
<leads>
<lead>
<lead_id>1</lead_id>
<action_cd>A</action_cd>
<calling_list_id>44</calling_list_id>
<mark_for_mail_ind>1</mark_for_mail_ind>
<contacts>
<contact>
<called_phone_number>8167142776</called_phone_number>
<call_ts>20050515130000</call_ts>
<caller_id>jsmith</caller_id>
<caller_nm>John Smith</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
<comment_txt>The customer says we rock</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>2</sec_call_result_cd>
<sec_call_result_cd>3</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
</contacts>
</lead>
<lead>
<lead_id>2</lead_id>
<action_cd>A</action_cd>
<calling_list_id>44</calling_list_id>
<mark_for_mail_ind>1</mark_for_mail_ind>
<contacts>
<contact>
<called_phone_number>8167142776</called_phone_number>
<call_ts>20050515130000</call_ts>
<caller_id>jsmith</caller_id>
<caller_nm>John Smith</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
<comment_txt>The customer says we rock</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>2</sec_call_result_cd>
<sec_call_result_cd>3</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
<contact>
<called_phone_number>8162221155</called_phone_number>
<call_ts>20050521130000</call_ts>
<caller_id>rmcintosh</caller_id>
<caller_nm>Rick Mcintosh</caller_nm>
<calling_gl_dept_id>24782</calling_gl_dept_id>
<pri_call_result_cd>1</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
</contact>
<contact>
<called_phone_number>9137142080</called_phone_number>
<call_ts>20050608091617</call_ts>
<caller_id>HHass</caller_id>
<caller_nm>Hanabal Hass</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>45</calling_list_id>
<comment_txt>The customer is always right</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
</contacts>
</lead>
</leads>
</ecm_data>'
exec sp_xml_preparedocument @.hdoc output, @.doc
SELECT *
FROM OPENXML(@.hdoc, '/ecm_data/leads/lead/contacts/contact', 2)
WITH ( id int '@.mp:id',
prev_id int '@.mp:prev',
parent_id int '@.mp:parentid',
lead_id integer '../../lead_id',
caller_id VARCHAR(7),
caller_nm VARCHAR(100),
calling_gl_dept_id INTEGER,
pri_call_result_cd INTEGER,
calling_list_id INTEGER
,sec_call_result_cd varchar(10)
'sec_call_result_cds/id')
/* --edge table
SELECT * FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds/*')
*/
SELECT * FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds/sec_call_result_c
d')
with ( id int '@.mp:id',parentid int '@.mp:parentid',
sec_call_result_cd varchar(10) '.')
EXEC sp_xml_removedocument @.hdocHere is the solution, with a cursor, which i was hoping to avoid...
--
If object_id('tempdb..#parent_rows') is not null
drop table #parent_rows
If object_id('tempdb..#childrows') is not null
drop table #childrows
declare @.doc varchar(8000)
declare @.hdoc integer
set @.doc =
'<?xml version="1.0" encoding="ISO-8859-1" ?>
<ecm_data>
<calling_lists>
<calling_list>
<calling_list_id>44</calling_list_id>
<list_nm>GLO Calling</list_nm>
<list_status_cd>1</list_status_cd>
</calling_list>
<calling_list>
<calling_list_id>45</calling_list_id>
<list_nm>GLO Vendor Calling</list_nm>
<list_status_cd>0</list_status_cd>
</calling_list>
</calling_lists>
<leads>
<lead>
<lead_id>1</lead_id>
<action_cd>A</action_cd>
<calling_list_id>44</calling_list_id>
<mark_for_mail_ind>1</mark_for_mail_ind>
<contacts>
<contact>
<called_phone_number>8167142776</called_phone_number>
<call_ts>20050515130000</call_ts>
<caller_id>jsmith</caller_id>
<caller_nm>John Smith</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
<comment_txt>The customer says we rock</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>2</sec_call_result_cd>
<sec_call_result_cd>3</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
</contacts>
</lead>
<lead>
<lead_id>2</lead_id>
<action_cd>A</action_cd>
<calling_list_id>44</calling_list_id>
<mark_for_mail_ind>1</mark_for_mail_ind>
<contacts>
<contact>
<called_phone_number>8167142776</called_phone_number>
<call_ts>20050515130000</call_ts>
<caller_id>jsmith</caller_id>
<caller_nm>John Smith</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
<comment_txt>The customer says we rock</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>2</sec_call_result_cd>
<sec_call_result_cd>3</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
<contact>
<called_phone_number>8162221155</called_phone_number>
<call_ts>20050521130000</call_ts>
<caller_id>rmcintosh</caller_id>
<caller_nm>Rick Mcintosh</caller_nm>
<calling_gl_dept_id>24782</calling_gl_dept_id>
<pri_call_result_cd>1</pri_call_result_cd>
<calling_list_id>44</calling_list_id>
</contact>
<contact>
<called_phone_number>9137142080</called_phone_number>
<call_ts>20050608091617</call_ts>
<caller_id>HHass</caller_id>
<caller_nm>Hanabal Hass</caller_nm>
<calling_gl_dept_id>24812</calling_gl_dept_id>
<pri_call_result_cd>5</pri_call_result_cd>
<calling_list_id>45</calling_list_id>
<comment_txt>The customer is always right</comment_txt>
<sec_call_result_cds>
<sec_call_result_cd>1</sec_call_result_cd>
<sec_call_result_cd>4</sec_call_result_cd>
</sec_call_result_cds>
</contact>
</contacts>
</lead>
</leads>
</ecm_data>'
exec sp_xml_preparedocument @.hdoc output, @.doc
SELECT * into #parent_rows
FROM OPENXML(@.hdoc, '/ecm_data/leads/lead/contacts/contact', 2)
WITH ( id int '@.mp:id',
prev_id int '@.mp:prev',
parent_id int '@.mp:parentid',
lead_id integer '../../lead_id',
caller_id VARCHAR(7),
caller_nm VARCHAR(100),
calling_gl_dept_id INTEGER,
pri_call_result_cd INTEGER,
calling_list_id INTEGER
,sec_call_result_cds varchar(10))
--SELECT * FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds/*')
SELECT * into #childRows
FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds/sec_call_result_c
d')
with ( id int '@.mp:id',parentid int '@.mp:parentid',
sec_call_result_cd varchar(10) '.')
--caller_id varchar(10) '../../caller_id')
EXEC sp_xml_removedocument @.hdoc
declare @.id integer
declare @.prev_id integer
declare @.counter integer
DECLARE parent_curser CURSOR
FOR SELECT id FROM #parent_rows order by id desc
OPEN parent_curser
set @.counter = 1
FETCH NEXT FROM parent_curser into @.id
WHILE @.@.FETCH_STATUS = 0
BEGIN
If @.counter > 1
Update #parent_rows
set prev_id = @.prev_id
where id = @.id
Else
Update #parent_rows
Set prev_id =
(select max(parentid)
From #childrows)
Where id = @.id
Set @.prev_id = @.id
Set @.counter = @.counter + 1
FETCH NEXT FROM parent_curser into @.id
END
CLOSE parent_curser
DEALLOCATE parent_curser
/*
select * from #parent_rows
select * from #childrows
*/
Select p.*, c. sec_call_result_cd
From #Parent_rows p
left join #childrows c on c.parentid between
p.id and p.prev_id