Problem Joining Parent and Children

Tech-Archive recommends: Fix windows errors by optimizing your registry

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_cd')
with ( id int '@mp:id',parentid int '@mp:parentid',
sec_call_result_cd varchar(10) '.')

EXEC sp_xml_removedocument @hdoc

.

Relevant Pages

  • Re: Problem with AppendChild
    ... The limitations are not so short as that, does your XML document have a DTD that says otherwise? ... It's not clear if CLIENT-SERVER-INTERFACE is the "parent" you speak of or is the node you are adding to something unmentioned. ... public class DomExample { ... DocumentBuilder builder = null; ...
    (comp.lang.java.help)
  • Re: Using XPath Against A Node
    ... top parent? ... Or is the entire xml document sent with it. ... In terms of the XPath implementation with SelectSingleNode and SelectNodes if the node is not inserted in the owning document then it looks like the XPath / evaluates to the node itself. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Please Help: SQLXML Bulk Load Error: Schema: relationship expected on Person
    ... <this is used when your xml document contains data for multiple tables in a ... Parent, child relationship> ... The following is the annotated schema: ... > No parent, ...
    (microsoft.public.sqlserver.xml)
  • Re: Using XPath Against A Node
    ... Or is the entire xml document sent with ... of that node was the new parent." ... Using "/" will search the document root node immediate descendants. ... run xpath statements against the xml file to grab data from it, so I use, ...
    (microsoft.public.dotnet.languages.vb)