HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
Message posted via http://www.droptable.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via droptable.com" <forum@.droptable.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQ
droptable.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.droptable.com
Showing posts with label joining. Show all posts
Showing posts with label joining. Show all posts
Friday, March 9, 2012
problem joining table is SQL query
HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
Message posted via http://www.sqlmonster.com
A NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.c om...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
Message posted via http://www.sqlmonster.com
A NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.c om...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com
problem joining table is SQL query
HI All,
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
--
Message posted via http://www.sqlmonster.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com
Can somebody help what's wrong with my SQL Query statement when i want to
create a join table query. It suppose work perfectly fine, but why it cant
work. The error that the ASP.Net throw out is "Object reference not set to
an instance of an object. " and "System.NullReferenceException: Object
reference not set to an instance of an object." at the line
dtrViewApplication.Close()
I have two table which are Job (JobID, JobTitle) and Application(JobID,
DateApplied, jobseekerID). I want both table field to be joined and display
by using Repeater.
Pls refer to the below for the source code.
Dim jobProvider As OleDbConnection
Dim cmdSelect As OleDbCommand
Dim dtrViewApplication As OleDbDataReader
Dim strSelect As String
jobProvider = New OleDbConnection
("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=C:\Inetpub\wwwroot\JobSeeker\
JobProvider.mdb")
strSelect = "select JobID, DateApplied, Job.JobTitle from
Application, Job where Application.JobID = Job.JobID And " & _
" jobSeekerID=@.jobSeekerID"
cmdSelect = New OleDbCommand(strSelect, jobProvider)
cmdSelect.Parameters.Add("@.LoginName", "JS12345")
Try
jobProvider.Open()
dtrViewApplication = cmdSelect.ExecuteReader
rptApplication.DataSource = dtrViewApplication
rptApplication.DataBind()
Catch ex As Exception
Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
ex.StackTrace & vbCrLf & ex.TargetSite.Name
Finally
dtrViewApplication.Close()
jobProvider.Close()
End Try
Thanks in advance
--
Message posted via http://www.sqlmonster.comA NullReferenceException means that your code is attempting to use an object
that has not yet been initialized. This may be dtrViewApplication since
it's neither declared nor instantiated in your code snippet. I suggest you
check to ensure dtrViewApplication is instantiated before setting the
DataSource property.
Also, this is a SQL Server forum but it appears you are using Access. The
problem seems to be related more to your ASP.NET code rather than data
access. You'll probably get more help in an ASP.NET forum.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"chng yeekhoon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f1dd26950b84414b87be4ebfe886a5ac@.SQLMonster.com...
> HI All,
> Can somebody help what's wrong with my SQL Query statement when i want to
> create a join table query. It suppose work perfectly fine, but why it cant
> work. The error that the ASP.Net throw out is "Object reference not set to
> an instance of an object. " and "System.NullReferenceException: Object
> reference not set to an instance of an object." at the line
> dtrViewApplication.Close()
> I have two table which are Job (JobID, JobTitle) and Application(JobID,
> DateApplied, jobseekerID). I want both table field to be joined and
> display
> by using Repeater.
> Pls refer to the below for the source code.
> Dim jobProvider As OleDbConnection
> Dim cmdSelect As OleDbCommand
> Dim dtrViewApplication As OleDbDataReader
> Dim strSelect As String
> jobProvider = New OleDbConnection
> ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
> Source=C:\Inetpub\wwwroot\JobSeeker\
> JobProvider.mdb")
> strSelect = "select JobID, DateApplied, Job.JobTitle from
> Application, Job where Application.JobID = Job.JobID And " & _
> " jobSeekerID=@.jobSeekerID"
>
> cmdSelect = New OleDbCommand(strSelect, jobProvider)
> cmdSelect.Parameters.Add("@.LoginName", "JS12345")
> Try
> jobProvider.Open()
> dtrViewApplication = cmdSelect.ExecuteReader
> rptApplication.DataSource = dtrViewApplication
> rptApplication.DataBind()
> Catch ex As Exception
> Label1.Text = ex.Message & vbCrLf & ex.Source & vbCrLf &
> ex.StackTrace & vbCrLf & ex.TargetSite.Name
> Finally
> dtrViewApplication.Close()
> jobProvider.Close()
> End Try
> Thanks in advance
> --
> Message posted via http://www.sqlmonster.com
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_cd')
with ( id int '@.mp:id',parentid int '@.mp:parentid',
sec_call_result_cd varchar(10) '.')
EXEC sp_xml_removedocument @.hdoc
Here 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_cd')
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
|||Hi Bob,
You can try a solution including one more level of LEFT JOIN to get to the
'contact' grandparentID.
Please run the following code and let me know if it returns the results you
are expecting:
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
(
(
SELECT *
FROM OPENXML(@.hdoc, '/ecm_data/leads/lead/contacts/contact', 2)
WITH ( id int '@.mp:id',
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))
) A
LEFT JOIN
(
SELECT X.sec_call_result_cd, Y.parentid as contact_id FROM
(
(
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) '.')
) X
LEFT JOIN
(
SELECT * FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds')
with ( id int '@.mp:id',parentid int '@.mp:parentid')
) Y
ON X.parentid = Y.id
)
)B
ON A.id = B.contact_id
)
EXEC sp_xml_removedocument @.hdoc
Thanks,
Ana Elisa - SDET - SQLServer Group
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob" wrote:
> Here 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_cd')
> 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
>
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
Here 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_cd')
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
|||Hi Bob,
You can try a solution including one more level of LEFT JOIN to get to the
'contact' grandparentID.
Please run the following code and let me know if it returns the results you
are expecting:
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
(
(
SELECT *
FROM OPENXML(@.hdoc, '/ecm_data/leads/lead/contacts/contact', 2)
WITH ( id int '@.mp:id',
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))
) A
LEFT JOIN
(
SELECT X.sec_call_result_cd, Y.parentid as contact_id FROM
(
(
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) '.')
) X
LEFT JOIN
(
SELECT * FROM OPENXML(@.hdoc,
'/ecm_data/leads/lead/contacts/contact/sec_call_result_cds')
with ( id int '@.mp:id',parentid int '@.mp:parentid')
) Y
ON X.parentid = Y.id
)
)B
ON A.id = B.contact_id
)
EXEC sp_xml_removedocument @.hdoc
Thanks,
Ana Elisa - SDET - SQLServer Group
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob" wrote:
> Here 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_cd')
> 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
>
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
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
Problem joining child data (JOIN, subquery, or something else?)
The problem:
I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.
I'm updating a report to be "multi-language" capable. Previously,
any items that had text associated with them were unconditionally
pulling in the English text. The database has always been capable of
storing multiple languages for an item, however.
Desired output:
Given the test data below, I'd like to get the following results
select * from mytestfunc(1)
Item_Id, Condition, QuestionText
1876, NOfKids <= 10, This many children is unlikely.
select * from mytestfunc(2)
CheckID, Condition, QuestionText
1876, NOfKids <= 10, NULL
The current SQL for my UDF:
CREATE FUNCTION Annotated_Check (@.Lang_ID int) RETURNS TABLE AS RETURN (
SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
tblQuestionText.QuestionText
FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
tblQuestionText.Item_ID)
WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
(tblQuestionText.LanguageReference IS NULL))
)
Test data:
CREATE TABLE [dbo].[tblCheck] (
[Item_ID] [int] NOT NULL ,
[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCheck] ADD
CONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED
(
[Item_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLanguage] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [int] NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Spoken] [bit] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADD
CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLanguage] ADD
CONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR
[CreationDate],
UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOCREATE TABLE [dbo].[tblQuestionText] (
[Item_ID] [int] NOT NULL ,
[LanguageReference] [int] NOT NULL ,
[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SameAs] [int] NULL ,
[CreationDate] [datetime] NULL ,
[RevisionDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblQuestionText] ADD
CONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR
[CreationDate],
CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED
(
[Item_ID],
[LanguageReference]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
INSERT INTO tblCheck (Item_ID, CheckDescr)
VALUES(1876, 'NOfKids <= 10')
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(1,1,'English', 1)
INSERT INTO tblLanguage (ID, Priority, Name, Spoken)
VALUES(2,2,'Espanol', 1)
INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)
VALUES (1876, 1, 'This many children is unlikely.')
Any tips or pointers will be appreciated. Thanks.Beowulf (beowulf_is_not_here@.hotmail.com) writes:
> I'm updating a report to be "multi-language" capable. Previously,
> any items that had text associated with them were unconditionally
> pulling in the English text. The database has always been capable of
> storing multiple languages for an item, however.
> Desired output:
> Given the test data below, I'd like to get the following results
Thanks for the extensive repro!
Then again, the fix is simple:
> CREATE FUNCTION Annotated_Check (@.Lang_ID int) RETURNS TABLE AS RETURN (
> SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
> tblQuestionText.QuestionText
> FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
> tblQuestionText.Item_ID)
> WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
> (tblQuestionText.LanguageReference IS NULL))
> )
Change WHERE to AND and skip last condition on IS NULL:
CREATE FUNCTION mytestfunc (@.Lang_ID int) RETURNS TABLE AS RETURN (
SELECT C.Item_ID, C.CheckDescr AS Condition, QT.QuestionText
FROM tblCheck C
LEFT JOIN tblQuestionText QT ON C.Item_ID = QT.Item_ID
AND QT.LanguageReference = @.Lang_ID
)
This is a classic error on the left join operator - yes, I did it
too! But once you understand it, it's apparent:
The whole FROM JOIN forms a table which is then filtered by WHERE.
In this case the LEFT JOIN as you had written it, never produced
any rows with NULL in the QuestionText columns, as there was a match
for all. But when you move the condition on language to the ON
clause, you only get a matych if the language is the desired one.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
>> <snip>
>> CREATE FUNCTION Annotated_Check (@.Lang_ID int) RETURNS TABLE AS RETURN (
>> SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,
>> tblQuestionText.QuestionText
>> FROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =
>> tblQuestionText.Item_ID)
>> WHERE ((tblQuestionText.LanguageReference = @.Lang_ID) OR
>> (tblQuestionText.LanguageReference IS NULL))
>> )
> Change WHERE to AND and skip last condition on IS NULL:
> CREATE FUNCTION mytestfunc (@.Lang_ID int) RETURNS TABLE AS RETURN (
> SELECT C.Item_ID, C.CheckDescr AS Condition, QT.QuestionText
> FROM tblCheck C
> LEFT JOIN tblQuestionText QT ON C.Item_ID = QT.Item_ID
> AND QT.LanguageReference = @.Lang_ID
> )
> This is a classic error on the left join operator - yes, I did it
> too! But once you understand it, it's apparent:
> The whole FROM JOIN forms a table which is then filtered by WHERE.
> In this case the LEFT JOIN as you had written it, never produced
> any rows with NULL in the QuestionText columns, as there was a match
> for all. But when you move the condition on language to the ON
> clause, you only get a matych if the language is the desired one.
Wow. My assumption was that I was going to have to get into some heavy
duty SQL hackery, but it really is quite simple. This even works
correctly if there actually is Spanish text. I had come up with
something of a workaround that would return NULL for me for other
languages if the only text was English, but it returned multiple records
if there was English and Spanish.
Thanks so much for the reply.
Problem joining 2 Views
Hi guys,
I detected a strange behaviour when doing an inner join of two views.
There is table_Objects, table_Contracts and view_Objects, View_Contracts.
Table_Objects: ID, Price
View_Objects: Select * from Table_Objects Where Price > 10
Table_Contracts: ID, Year
View_Contracts: Select c.ID, c.Year
From Table_Contracts c Join View_Objects o on c.id=o.id
Quiete simple so far, but it doesn't work. Imagine Object with id 3 that is shown in View_Objects and Table_Contracts but not shown in View_Contracts. How is that possible?
EDIT: Sometimes its shown, sometimes not
Thx for your reply!Is Table_Contracts.ID a primary key or a foreign key to Table_Objects?|||Table_Contract's PK is (ID,Year)
Table_Object's PK is (ID)
There is a FK from Table_Contract(ID) to Table_Objects(ID)|||Oh, just realized that I'm joining a view and a table and not 2 views.
I detected a strange behaviour when doing an inner join of two views.
There is table_Objects, table_Contracts and view_Objects, View_Contracts.
Table_Objects: ID, Price
View_Objects: Select * from Table_Objects Where Price > 10
Table_Contracts: ID, Year
View_Contracts: Select c.ID, c.Year
From Table_Contracts c Join View_Objects o on c.id=o.id
Quiete simple so far, but it doesn't work. Imagine Object with id 3 that is shown in View_Objects and Table_Contracts but not shown in View_Contracts. How is that possible?
EDIT: Sometimes its shown, sometimes not
Thx for your reply!Is Table_Contracts.ID a primary key or a foreign key to Table_Objects?|||Table_Contract's PK is (ID,Year)
Table_Object's PK is (ID)
There is a FK from Table_Contract(ID) to Table_Objects(ID)|||Oh, just realized that I'm joining a view and a table and not 2 views.
Subscribe to:
Posts (Atom)