Problem returning two values from stored procedures

Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.

My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:\blah'

Please could anyone shed ligth on this problem?

here the code for the stored procedure:

CREATE PROCEDURE dbo.GetTillInfo
(
@.TillIdR varchar(50),
@.Password varchar(50)
)
AS

declare @.TillId int
declare @.configpath varchar(150)

IF Exists (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
BEGIN

set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
select @.TillIdR as 'TId'

set @.configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @.login)
select @.configpath as 'ConfigPath'
END
ELSE
BEGIN
set @.TillIdR = 0
select @.TillIdR as 'TId'
set @.configpath =''
select @.configpath as 'ConfigPath'
END
GOOff the top of my head, the two results may be returned but in two tables as you are performing two selects.

To get round this you could change your select query to return the two values like:-

IF ...
 set @.TillIdR = (SELECT Id FROM Tills WHERE TillRef=@.TillId and TillPassword=@.Password)
 set @.configpath = (SELECT configpath from customer,tills where
 tills.customerid = customer.id and tills.id = @.login)
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
ELSE
BEGIN
 set @.TillIdR = 0
 set @.configpath =''
 select @.TillIdR as 'TId', @.configpath as 'ConfigPath'
END
GO

This is off the top of my head at work - you may have to play with the stored proc.

Rob

problem returning rows from SPROC

Heres my problem, the first part selects a row from the database, if
there is no row with the criteria it inserts a row and then returns it,
the problem is the IF statement that inserts the row, never returns the
select after it. if there is a row initially in the database, it
returns the right information, I just can't get it to return the row
after inserting it. Anyone know what the problem could be?

Stored procedure:

ALTER PROCEDURE dbo.CheckCurrentPayPeriod
(@.UserID varchar(50))
AS

BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return

DECLARE @.appStartDate DATETIME
DECLARE @.dt DATETIME
DECLARE @.rows int
SET @.appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @.dt = GETDATE()

SELECT
UserID
FROM
PayPeriod
WHERE
(PeriodStart <= CONVERT(varchar(10), @.dt, 101)) AND (PeriodEnd >=
CONVERT(varchar(10), @.dt, 101)) AND (UserID = @.UserID)

-- Inserts their new PayPeriod
DECLARE @.PayPeriodID int
if (@.@.ROWCOUNT = 0)
BEGIN
DECLARE @.sDate datetime
DECLARE @.eDate datetime
SET @.sDate = @.appStartDate
SET @.eDate = DATEADD(day, 13, @.sDate)

INSERT INTO
PayPeriod
(UserID, PeriodStart, PeriodEnd)
VALUES
(@.UserID, @.sDate, @.eDate)

/*EXEC @.PayPeriodID = InsertPayPeriod @.UserID, @.sDate, @.eDate*/

SET @.PayPeriodID = @.@.IDENTITY

SELECT * FROM PayPeriod WHERE PayPeriodID = @.PayPeriodID

RETURN

END
else
RETURN
END(dkode8@.gmail.com) writes:
> Heres my problem, the first part selects a row from the database, if
> there is no row with the criteria it inserts a row and then returns it,
> the problem is the IF statement that inserts the row, never returns the
> select after it. if there is a row initially in the database, it
> returns the right information, I just can't get it to return the row
> after inserting it. Anyone know what the problem could be?

So how do you run the procedure? If you run it from Query Analyzer,
you will see something like:

UserID
--

(0 rows affected)

(1 row affected)

PayPeriodID UserID PeriodStart PeriodEnd
---- -- ---- ---
1 <value> <value> <value
(1 row affected)

If you run this from a client program, you must be able to handle these
three result sets. This means that if you use ADO - to take an example -
you should have to use .NextRecordset to navigate through the record sets.
Note here that the second record set is closed - that record sets consists
of the rowcount only.

However, it's probably better to rewrite the procedure:

CREATE PROCEDURE dbo.CheckCurrentPayPeriod (@.UserID varchar(50)) AS
BEGIN
-- This SP checks to see if the current PayPeriod exists,
-- if not it will create the payperiod for them and return
DECLARE @.appStartDate DATETIME
DECLARE @.dt DATETIME
DECLARE @.rowc int
DECLARE @.PayPeriodID int

SET NOCOUNT ON

SET @.appStartDate = (SELECT PayPeriodStart FROM PayPeriodStart)
SET @.dt = GETDATE()

SELECT @.PayPeriodID = PayPeriodID
FROM PayPeriod
WHERE PeriodStart <= CONVERT(char(8), @.dt, 112)
AND PeriodEnd >= CONVERT(char(8), @.dt, 112)
AND UserID = @.UserID
SELECT @.rowc = @.@.rowcount

-- Inserts their new PayPeriod
IF @.rowc = 0
BEGIN
INSERT INTO PayPeriod (UserID, PeriodStart, PeriodEnd)
VALUES (@.UserID, @.appStartDate, DATEADD(day, 13, @.sDate))
SET @.PayPeriodID = @.@.IDENTITY
END

SELECT PayPeriodId, UserId, PeriodStart, PeriodEnd
FROM PayPeriod
WHERE PayPeriodID = @.PayPeriodID
END

Observations:

o SET NOCOUNT ON removes the closed recordset for the rowcount from
the INSERT statement.

o Use style 112 when chopping of time from datetime values. 112 gives
you the format YYYYMMDD, which is always interpreted the same. Format
could be reinterpreted if the user has an unexpected language setting.

o Since @.@.rowcount is volatile - update after each statement, I catch
into a local variable immeidately, and glue that SELECT directly to
the SELECT I'm catching rowcount for.

o SELECT * in production is not good practice. Always explicitly list
which columns you want returned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Problem returning data

I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:

ALTER PROCEDURE

dbo.usp_CalcDeliveryCharge

@.mintDistance

int,

@.mintCustomer_ID

int= 0,

@.mintRate

int= 0OUTPUT,

@.mstrZone

nchar(10) =null OUTPUT

AS

/* SET NOCOUNT ON */SELECT@.mintRate=RATE,

@.mstrZone=ZONE

FROMtblRatesWHERECustomer_ID=@.mintCustomer_IDANDMile_Range_Min <= @.mintDistanceANDMile_Range_Max >= @.mintDistance

And this is my code:

sql_Command.CommandText =

"usp_CalcDeliveryCharge"

sql_Command.CommandType = CommandType.StoredProcedure

sql_Command.Parameters.Clear()

sql_Command.Parameters.AddWithValue(

"@.mintDistance", intApproxMiles)

sql_Command.Parameters.AddWithValue(

"@.mintCustomer_ID", Profile.CompanyID)

sql_Conn.Open()

sql_Reader = sql_Command.ExecuteReader()

While (sql_Reader.Read())Me.lblZone.Text = sql_Reader.Item(0).ToStringEndWhile

sql_Conn.Close()

sql_Reader.Close()

sql_Command.Dispose()

If you are using OUTPUT parameters you need to add the output parameters in the asp.net code also and set their direction as output and retrieve the values through those parameters not through datareader.
checkthis article if it helps.

Problem Ordering XML using ADO.Net

I am converting some legacy ado code to ado.net. This function uses
'for xml explict' and returns the string representation of the xml. I
add a root node (<sales> ) around that string and load into an xml
document. It would return the following:
<sales>
<transaction>
<terms/>
<terms/>
</transaction>
<transaction>
<terms/>
<terms/>
</transaction>
</sales>
My new code looks like this, it uses the exact same SQL query, The
variable 'RootNode' is passed in and is set to 'sales':
Command = New SqlCommand
Command.CommandText = SQL & ", XMLDATA"
Command.CommandType = CommandType.Text
Command.Connection = Connection
Try
xrReader = Command.ExecuteXmlReader()
Try
Dim ds As DataSet = New DataSet
ds.ReadXml(xrReader, XmlReadMode.Fragment)
ds.DataSetName = RootNode.ToString
Dim DataDoc As XmlDataDocument = New
XmlDataDocument(ds)
strReturn = DataDoc.InnerXml
Catch ex As Exception
strReturn = "</" & RootNode & ">"
End Try
Catch ex As Exception
SaveEvent("CommonADO", "Application", ex.Message,
EventLogEntryType.Error, "EMK3Common.dll")
End Try
This function returns strReturn and looks like the following:
<sales>
<transaction/>
<transaction/>
<terms/>
<terms/>
<terms/>
<terms/>
</sales>
As you can see it does not return the same value. I'm hoping someone
can see what I've done wrong and suggest a fix that will return the
same thing as my old ado dll.
Thanks for your help.Can you post the SQL, i.e. whatever 'SQL' is in the
statement below
Command.CommandText = SQL & ", XMLDATA"
Chances are the ORDER BY clause is incorrect or
missing.|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate]|||The SQL is posted below. It returns the data in the correct format
when running it through query analyzer and has worked correctly for the
past two years. The problem seems to be in the conversion from dataset
to xml. Thanks for looking at this.
SELECT 1 AS Tag, NULL AS Parent, lc.TransactionID AS
[transaction!1!TransactionID], lc.ContractID AS
[transaction!1!ContractID],
lc.ContractBeginDate AS
[transaction!1!ContractBeginDate], lc.ContractEndDate AS
[transaction!1!ContractEndDate],
lc.TerminationDate AS
[transaction!1!TerminationDate], lc.LeaseID AS [transaction!1!LeaseID],
lc.NetEstimatedMCFsDay AS
[transaction!1!NetEstimatedMCFsDay], lc.BTUValue AS
[transaction!1!BTUValue],
lc.ServiceType AS [transaction!1!ServiceType],
lc.EstimatedVolumeSell AS [transaction!1!EstimatedVolumeSell],
lc.EstimatedVolumeSell2 AS
[transaction!1!EstimatedVolumeSell2], lc.AutomaticExtensionType AS
[transaction!1!AutomaticExtensionType],
lc.AutomaticExtensionInstr AS
[transaction!1!AutomaticExtensionInstr],
lc.ContractInstr AS
[transaction!1!ContractInstr], lc.EFP AS [transaction!1!EFP],
lc.ConfirmationNumber AS
[transaction!1!ConfirmationNumber], lc.AnalysisDate AS
[transaction!1!AnalysisDate],
lc.ConfirmationDate AS
[transaction!1!ConfirmationDate], lc.BuyerOperationsName AS
[transaction!1!BuyerOperationsName],
lc.BuyerOperationsPhone AS
[transaction!1!BuyerOperationsPhone], lc.BuyerOperationsFax AS
[transaction!1!BuyerOperationsFax],
lc.InvoiceVolumeBasedOn AS
[transaction!1!InvoiceVolumeBasedOn], c.ContractType AS
[transaction!1!ContractType],
l.LeaseType AS [transaction!1!LeaseType],
c.LocalBuyerID AS [transaction!1!LocalBuyerID], c.ProducerID AS
[transaction!1!ProducerID],
c.ContractNo AS [transaction!1!ContractNo],
lc.TransactionID AS [terms!2!TransactionID], NULL AS [terms!2!TermsID],
NULL AS [terms!2!BidValue], NULL
AS [terms!2!BidPercent], NULL AS
[terms!2!PriceBasis], NULL AS [terms!2!NYMEXTriggerRights], NULL AS
[terms!2!NYMEXTriggerParam], NULL
AS [terms!2!NYMEXTriggerDeadline], NULL AS
[terms!2!IndexType1], NULL AS [terms!2!IndexPosting1], NULL AS
[terms!2!TermsBeginDate], NULL
AS [terms!2!TermsEndDate], NULL AS
[terms!2!PercentofProceedsType], NULL AS [terms!2!PercentofProceeds],
NULL
AS [terms!2!NYMEXTriggerPrice], NULL AS
[terms!2!NYMEXTriggerOn], NULL AS [terms!2!NYMEXPriceBasis], NULL
AS [terms!2!AOCalculationType], NULL AS
[terms!2!AOPriceScheduleID1], NULL AS [terms!2!AOPriceScheduleID2],
NULL
AS [terms!2!AOPriceScheduleID3], NULL AS
[terms!2!VolumeCriteria], NULL AS [terms!2!VolumeMin], NULL AS
[terms!2!VolumeMax], NULL
AS [terms!2!VolumeUnit]
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
GROUP BY lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo
UNION
SELECT 2, 1, lc.TransactionID, lc.ContractID, lc.ContractBeginDate,
lc.ContractEndDate, lc.TerminationDate, lc.LeaseID,
lc.NetEstimatedMCFsDay, lc.BTUValue,
lc.ServiceType, lc.EstimatedVolumeSell,
lc.EstimatedVolumeSell2, lc.AutomaticExtensionType,
lc.AutomaticExtensionInstr, lc.ContractInstr, lc.EFP,
lc.ConfirmationNumber, lc.AnalysisDate,
lc.ConfirmationDate, lc.BuyerOperationsName, lc.BuyerOperationsPhone,
lc.BuyerOperationsFax,
lc.InvoiceVolumeBasedOn, c.ContractType,
l.LeaseType, c.LocalBuyerID, c.ProducerID, c.ContractNo,
lct.TransactionID, lct.TermsID, lct.BidValue,
lct.BidPercent, lct.PriceBasis,
lct.NYMEXTriggerRights, lct.NYMEXTriggerParam,
lct.NYMEXTriggerDeadline, lct.IndexType1, lct.IndexPosting1,
lct.TermsBeginDate, lct.TermsEndDate,
lct.PercentofProceedsType, lct.PercentofProceeds,
lct.NYMEXTriggerPrice, lct.NYMEXTriggerOn,
lct.NYMEXPriceBasis, lct.AOCalculationType,
lct.AOPriceScheduleID1, lct.AOPriceScheduleID2, lct.AOPriceScheduleID3,
lct.VolumeCriteria,
lct.VolumeMin, lct.VolumeMax, lct.VolumeUnit
FROM gmm_lease l INNER JOIN
gmm_contracttransaction lc ON (l.SellerID =
lc.SellerID AND l.LeaseID = lc.LeaseID) INNER JOIN
gmm_contract c ON (lc.SellerID = c.SellerID AND
lc.ContractID = c.ContractID) INNER JOIN
gmm_contracttransactionterms lct ON (lc.SellerID
= lct.SellerID AND lc.ContractID = lct.ContractID AND lc.TransactionID
= lct.TransactionID)
WHERE l.SellerID = 29 AND c.ContractType = 1 AND
(lc.ContractBeginDate <= '5/31/2005' AND (lc.TerminationDate >=
'5/1/2005' OR
lc.TerminationDate IS NULL)) AND l.LeaseID = 2066
ORDER BY [transaction!1!LeaseID], [transaction!1!TransactionID],
[terms!2!TransactionID], [terms!2!TermsBeginDate] FOR XML EXPLICIT|||Your SQL looks okay. Suggest you post to an ADO.NET newsgroup.|||I've had another look at this and think that
problem is that the relationship between transaction
and terms isn't coming through from the query. One
solution is to add it yourself by supplying a new
DataRelation object
After
ds.DataSetName = RootNode.ToString
add this (this is C#, but should translate to VB.NET easily)
DataColumn parentCol =
ds.Tables["transaction"].Columns["TransactionID"];
DataColumn childCol = ds.Tables["terms"].Columns["TransactionID"];
DataRelation TransactionTerms = new DataRelation("TransactionTerms",
parentCol, childCol);
ds.Relations.Add(TransactionTerms);
TransactionTerms.Nested = true;

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
>

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