Calculated measure

I have multiple fact tables very much like the internet-sales store-sales scenario described in the analysis services tutorial:

http://msdn2.microsoft.com/en-us/library/ms166568(SQL.90).aspx

I've created a calculated measure for total revenue (= [Ad Revenue] + [Preprint Revenue]) This works fine when the fact tables share a dimension such as customer. There are, however, dimensions that are specific to each of the fact tables

For example, if I display revenue by customer in the browser, I correctly get the sum of ad revenue and preprint revenue for total revenue. If I display revenue by Ad Product (dimension specifc to ads and not preprints) the total revenue figure includes the ad revenue that is specifc to the Ad product and all preprint revenue.

That's potetnially confusing for end users. Is there a way to hide the calculation when certain dimesnsions are selected or to convert the preprint revenue to 0?

thanks

Peter

Hi Peter,

For the Measure Group which includes [Preprint Revenue], will setting the IgnoreUnrelatedDimensions property to False achieve the desired result?

http://msdn2.microsoft.com/en-us/library/ms365411.aspx

>>

Configuring Measure Group Properties

IgnoreUnrelatedDimensions

Determines whether unrelated dimensions are forced to their top level when members of dimensions that are unrelated to the measure group are included in a query. Default setting is True.

>>

|||

Thanks Deepak. That was the answer.

Peter

calculated fields w/user defined functions

Hi,
In the new application we are extensively using calculated fields that
consist of user defined functions that query multiple tables in the
database. Does anybody know of any limitations or drowbacks of using
user-defined functions in calculated fields (performance, locking, etc) Here
is an example of the most complex user defined function we have:
CREATE FUNCTION dbo.PayrollDetail_Subtotal
(
@.PayrollDetailID int,
@.CoverageType varchar(25)
)
RETURNS decimal(19,4) AS
BEGIN
DECLARE
@.AmountCap AS decimal(19,2),
@.AmountEffective as decimal (19,2),
@.AssignmentID as int,
@.PolicyID1 as int,
@.PolicyID2 as int,
@.PolicyTo1 as datetime,
@.PolicyFrom2 as datetime,
@.WorkCode AS int,
@.Result AS decimal(19,2),
@.PayrollFrom AS datetime,
@.PayrollTo AS datetime,
@.PayrollType AS varchar(50),
@.PayrollHeaderID AS int,
@.Composite1 AS decimal(19,2),
@.Composite2 AS decimal(19,2),
@.Territory1 AS int,
@.TerritoryMod1 as decimal(10,4),
@.WCRateID1 AS int,
@.WCRate1 AS decimal(19,4),
@.WCFactor1 AS varchar(50)
SET @.Result = 0
/* Payroll Header ID and dates*/
/* NULL for PayrollHeader is not allowed in tblPayrollDetail */
SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
@.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
/* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType = PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
PayrollHeaderID = @.PayrollHeaderID
SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
@.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
@.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
/* Composite Rating in tblCoverageEntry has default of 0 */
if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
(@.Composite2=0)
return 0
ELSE
BEGIN
IF (@.PolicyID1 = @.PolicyID2)
/* ++++++CASE 1 : Payroll dates fall within the same policy year
+++++++*/
BEGIN
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
return 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result = @.WCRate1*0.001
ELSE
SELECT @.Result = @.WCRate1
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result = @.Result*@.AmountEffective
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result = @.Result * @.AmountEffective
ELSE
SELECT @.Result = @.Result * @.AmountCap
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result = @.Result * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result = @.Result * @.Composite1
ELSE
SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
END
END
END
ELSE
/* IF Payroll falls under 2 policy years */
BEGIN
DECLARE
@.Territory2 AS int,
@.TerritoryMod2 as decimal(10,4),
@.WCRateID2 AS int,
@.WCRate2 AS decimal(19,4),
@.WCFactor2 AS varchar(50),
@.Result1 AS decimal(19,4),
@.Result2 AS decimal(19,4),
@.NoDays1 AS int,
@.NoDays2 AS int,
@.PayrollLength AS int
/* +++POLICY YEAR 1+++ */
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
SELECT @.Result1 = 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result1 = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result1 = @.WCRate1*0.001
ELSE
SELECT @.Result1 = @.WCRate1
END
/* base */
SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
SELECT @.PayrollLength = @.NoDays1+@.NoDays2
/* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
Year 1 */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
ELSE
SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
END
END
/* +++POLICY YEAR 2+++ */
/* work code rate */
SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID2 is null)
SELECT @.Result2 = 0
ELSE
BEGIN
IF @.WCFactor2 = 'Rate/100'
SELECT @.Result2 = @.WCRate2*0.01
ELSE
BEGIN
IF @.WCFactor2='Rate/1000'
SELECT @.Result2 = @.WCRate2*0.001
ELSE
SELECT @.Result2 = @.WCRate2
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
ELSE
SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
END
/* Territory */
SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory2 is null)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
BEGIN
SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
END
END
SELECT @.Result = @.Result1 + @.Result2
END
END
return @.Result
ENDUser defined functions in computed columns perform pretty badly compared to
plain SQL. The reason is that behind the season the function works as a
cursor, all the work is done on a row-by-row basis. You probably won't
notice that much if you only have a few rows, but once you get to a largish
amount (say 1000+) performance will go out of the window.
I did some testing the other day and SELECT * on a table with around 8000
rows had a subsecond return time without a computed column with a UDF, but
20+ seconds with a computed column with a UDF. On that basis I decided to
redesign the solution so it would work without a computed column, which
involved creating a few new tables, and performance is now acceptable.
So, you can use it, but only if your table has a very small number of rows,
or if you have a larger table and you are sure you will never ever have a
table scan on that table.
"ilona" <ieshulman@.sseinc.com> wrote in message
news:etjJPzjRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In the new application we are extensively using calculated fields that
> consist of user defined functions that query multiple tables in the
> database. Does anybody know of any limitations or drowbacks of using
> user-defined functions in calculated fields (performance, locking, etc)
Here
> is an example of the most complex user defined function we have:
>
> CREATE FUNCTION dbo.PayrollDetail_Subtotal
> (
> @.PayrollDetailID int,
> @.CoverageType varchar(25)
> )
> RETURNS decimal(19,4) AS
> BEGIN
> DECLARE
> @.AmountCap AS decimal(19,2),
> @.AmountEffective as decimal (19,2),
> @.AssignmentID as int,
> @.PolicyID1 as int,
> @.PolicyID2 as int,
> @.PolicyTo1 as datetime,
> @.PolicyFrom2 as datetime,
> @.WorkCode AS int,
> @.Result AS decimal(19,2),
> @.PayrollFrom AS datetime,
> @.PayrollTo AS datetime,
> @.PayrollType AS varchar(50),
> @.PayrollHeaderID AS int,
> @.Composite1 AS decimal(19,2),
> @.Composite2 AS decimal(19,2),
> @.Territory1 AS int,
> @.TerritoryMod1 as decimal(10,4),
> @.WCRateID1 AS int,
> @.WCRate1 AS decimal(19,4),
> @.WCFactor1 AS varchar(50)
>
> SET @.Result = 0
> /* Payroll Header ID and dates*/
> /* NULL for PayrollHeader is not allowed in tblPayrollDetail */
> SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
> @.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
> tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
> /* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
> SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType => PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
> PayrollHeaderID = @.PayrollHeaderID
> SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
> @.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
> SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
> @.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
> /* Composite Rating in tblCoverageEntry has default of 0 */
> if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
> (@.Composite2=0)
> return 0
> ELSE
> BEGIN
> IF (@.PolicyID1 = @.PolicyID2)
> /* ++++++CASE 1 : Payroll dates fall within the same policy year
> +++++++*/
> BEGIN
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> return 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result = @.WCRate1*0.001
> ELSE
> SELECT @.Result = @.WCRate1
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result = @.Result*@.AmountEffective
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result = @.Result * @.AmountEffective
> ELSE
> SELECT @.Result = @.Result * @.AmountCap
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
> END
> END
> END
> ELSE
> /* IF Payroll falls under 2 policy years */
> BEGIN
> DECLARE
> @.Territory2 AS int,
> @.TerritoryMod2 as decimal(10,4),
> @.WCRateID2 AS int,
> @.WCRate2 AS decimal(19,4),
> @.WCFactor2 AS varchar(50),
> @.Result1 AS decimal(19,4),
> @.Result2 AS decimal(19,4),
> @.NoDays1 AS int,
> @.NoDays2 AS int,
> @.PayrollLength AS int
>
> /* +++POLICY YEAR 1+++ */
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> SELECT @.Result1 = 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result1 = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result1 = @.WCRate1*0.001
> ELSE
> SELECT @.Result1 = @.WCRate1
> END
> /* base */
> SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
> SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
> SELECT @.PayrollLength = @.NoDays1+@.NoDays2
> /* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
> Year 1 */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> ELSE
> SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
> END
>
> END
> /* +++POLICY YEAR 2+++ */
> /* work code rate */
> SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID2 is null)
> SELECT @.Result2 = 0
> ELSE
> BEGIN
> IF @.WCFactor2 = 'Rate/100'
> SELECT @.Result2 = @.WCRate2*0.01
> ELSE
> BEGIN
> IF @.WCFactor2='Rate/1000'
> SELECT @.Result2 = @.WCRate2*0.001
> ELSE
> SELECT @.Result2 = @.WCRate2
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> ELSE
> SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
> END
> /* Territory */
> SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory2 is null)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> BEGIN
> SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
> IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
> END
> END
> SELECT @.Result = @.Result1 + @.Result2
> END
> END
> return @.Result
>
> END
>sql

calculated fields

Hey All,
I wondering how you do a calculated field in Reporting
Services. I need to be able to iterate through two
seperate tables and figure out the activation rate. So I
thought I could use a calcuated field but when I do it, it
tries to apply the sum function when I drag that field out
of the fields list. When I remove the sum function I get
an error.
= Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
don't get it how do I do this?I'm afraid that's not possible.
You'll need to calculate it in a query
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c472c5$eb5b27c0$a401280a@.phx.gbl...
> Hey All,
> I wondering how you do a calculated field in Reporting
> Services. I need to be able to iterate through two
> seperate tables and figure out the activation rate. So I
> thought I could use a calcuated field but when I do it, it
> tries to apply the sum function when I drag that field out
> of the fields list. When I remove the sum function I get
> an error.
> = Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
> don't get it how do I do this?

Calculate the values from a underlying table

I am building a customerlist within the customer sales of a period. I have a dataset with two tables: "customer" and "customer_ledger_entry".

In the report I will present the customer number, the customer name and the sales. The problem is the sales value is not available as a field, but I have to calculate this value from the "customer_ledger_entry" table. In this table are several entries (invoices, credit notes, etc.)

How to calculate the values from a underlying table?

Here is a post that may work. You would populate the sales value into a dictionary object and then reference it based on the key you decide to use.

(from my blog at http://sqlrs.blogspot.com)

One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.

What if you don't want to or can't?

Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.

Steps to lookup values from a reference table in SQL:

Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)

Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")

In the Code Properties window, try the following:

public dict as new System.Collections.Generics.Dictionary(Of System, System)

function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function

function getValue(value as object) as object
return dict(value)
end function

Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.

If you have properly bound a table to the first list control, you should be able to lookup results in the second table.

This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.

I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.

Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.

cheers,

Andrew

|||

Selectis,

I believe you may want to create 2 dataset for your report, the first dataset for the "customer" and the second dataset for "customer_ledger_entry". You can then calculate your values like SUM(Fields!Sales.value,"customer_ledger_entry", customer name like =(Fields!FirstName,"customer")

I hope this was what you were looking for.

Ham

|||

I've made a the two datasets like you said. My expression is:

=Sum(Fields!Sales__LCY_.Value, "customer_ledger_entry"), (Fields!Customer_No_.Value, "customer_ledger_entry") like = (Fields!No_.Value, "customer")

When I run the report I get the message "The Value expression for the textbox refers to a the field "Customer_No_". Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

|||

Selectis,

I must have misunderstood what you were trying to accomplish, I thought you when trying to display values from 2 different tables but I didn’t realize you want to reference the 2 datasets together.

If you can do the following:

You can nest data regions within other data regions. For example, if you want to create a sales record for each sales person in a database, you can create a list with text boxes and an image to display information about the employee, and then add table and chart data regions to show the employee's sales record.

I hope this helps

Ham

|||

I′ve found a way to calculate the value: I use one dataset with two tables: "customer" and "customer_ledger_entry".

The report contains a table and I've used the SUM function for the customer sales: =SUM(Fields!Sales.Value). Now my report calculates all the sales lines from the "customer_ledger_entry". That is not what I want. But the solution is to choose "Edit Group" for the detail line in your report. In the Group settings I added a sorting on the customer and I added a "Group on" expression. Now the SUM function only calculates the records for the customer listed on the line.

Cache Database structure (How to detect if database-design has changed..)

Hello everyone,

I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.

I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.

I want to know if a column or table has changed any values, like datatype, name, properties, etc.

Any suggestions out there ?!Sure, use a DDL trigger. Anytime something changes that you need to know about insert a record into another table (tblDDLChanged?). Then use a SqlDependancy object on the tblDDLChanged table? Although you might be able to just use a set of SqlDependancy objects to monitor the base tables themselves, but I'm not sure if it detects DDL changes or not.|||I am not able to create any triggers only able to read the tables.|||After looking at the system tables I found the answer.

You retreive when last change was done to a column in a database by the following query:

SELECT TOP 1 sysobjects.refdate FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id ORDER BY sysobjects.refdate DESC

c# and SQL query

Using Visual Studio 2005 c#

I am trying to create a query to one of my tables in my database. It looks something like this-

SELECT ...

FROM ...

WHERE TranDetail.EndDateTime = TranHeader.EndDateTime

Now that works fine... but i need to make another query... that would look something like this.

SELECT...

FROM...

WHERE TranDetail.EndDateTime = serial.

Serial is a variable in my current c# coding. I know in other languages they use stuff like ':' or'@.'.. but i dont know how to do this in c#.

you can use parameterized query. set your comment text as

SELECT...FROM...WHERE TranDetail.EndDateTime = @.serial

and

Command.Parameters.Add("@.serial", yourC#Variable);
You have to make sure type is match.

Hope this help

business logic in Stored Proc VS aspx.vb page

Hello,

I stuck in a delimma.
Where to put the business logic that involves only one update
but N number of selects from N tables......with N where conditionsHi

In general I would put this in the middle tier or the database. Allowing
direct access to tables from a client would raise security issues and make
it less managable.

John

"A.V.C." <yhspl_softwaregroup@.hotmail.com> wrote in message
news:d28fa5d0.0407060443.2797e77@.posting.google.co m...
> Hello,
> I stuck in a delimma.
> Where to put the business logic that involves only one update
> but N number of selects from N tables......with N where conditions|||A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> I stuck in a delimma.
> Where to put the business logic that involves only one update
> but N number of selects from N tables......with N where conditions

I am of the school that puts as much as possible of the business logic
in the stored procedures. The idea is to put the logic where the data is.
If you put logic in the middle tier, you may have a lot network traffic.

The one case where the middle layer is a better place, is when you
have computations that are very intensive on reports. Then you can
take off load from SQL Server, and you can more easilyu scale out.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns951EF3408DA66Yazorman@.127.0.0.1...
> A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> > I stuck in a delimma.
> > Where to put the business logic that involves only one update
> > but N number of selects from N tables......with N where conditions
> I am of the school that puts as much as possible of the business logic
> in the stored procedures. The idea is to put the logic where the data is.
> If you put logic in the middle tier, you may have a lot network traffic.

Which would not have otherwise existed if the logic is internal
to the database stored procedure.

> The one case where the middle layer is a better place, is when you
> have computations that are very intensive on reports. Then you can
> take off load from SQL Server, and you can more easilyu scale out.

Where separate pre-processing (ie: regeneration of the computed
values) into a work table is feasible, then this can allow the logic to
be retained in SQL because the realtime computations have been
reduced.|||THanx
Could you pls elaborate the scenario where middle tier is ideal for business logic ?

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns951EF3408DA66Yazorman@.127.0.0.1>...
> A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> > I stuck in a delimma.
> > Where to put the business logic that involves only one update
> > but N number of selects from N tables......with N where conditions
> I am of the school that puts as much as possible of the business logic
> in the stored procedures. The idea is to put the logic where the data is.
> If you put logic in the middle tier, you may have a lot network traffic.
> The one case where the middle layer is a better place, is when you
> have computations that are very intensive on reports. Then you can
> take off load from SQL Server, and you can more easilyu scale out.|||A.V.C. (yhspl_softwaregroup@.hotmail.com) writes:
> Could you pls elaborate the scenario where middle tier is ideal for
> business logic ?

Permit me to take an example from the system I work with, which is abour
securities trading. When a deal comes in there are a couple of computations
to carry out give the basics: price and the quantity. Most of these
computations are simple: price * qty gives your the purchase amount, and
then you compute charges. And to compute charges you require access to
data, because the charges depends on the customer and instrument and this
is information that is in several tables.

The one exception here is when you trade with bonds, because they may be
traded on interest rather than price, in which case the price has to be
computed from the interest. You also have to compute the accrued interest
for the bond, that the seller is to pay to the buyer. These computations
are not very simple to implement in SQL. What we do is that we call a
COM object that runs on the SQL Server machine that performs the
computation, so we still have this logic on the server.

Our customers have fairly modest volume of bond deals, so this is not an
issue. But assume that you have a site that trades exclusively in bonds,
and can perform many trades a second (not very likely, I think). In this
case, having the COM object on the server would take some toll that would
be bearable. We could make the COM object remote, but it would still be
one server. If instead the middle tier would get all trades to compute,
there could be several machines that each gets their load, so you can scale
better.

It's maybe not the best example, but I think it gives you the idea that
even if you put some logic in the middle tier, it may not be all logic,
but only some specific part.

Finally one more argument for having the logic in stored procedures: you
have all the code in one place. If you use the middle tier, you will jump
forth and back in different languages and the code is more difficult to
follow, debug and maintain.

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

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

Businees Intelligence: Is it possible to return more than one tables in dataset

Is it possible to return a dataset contain more than one tables inside it....... andreceive in reporting services?

You can perform a join in your query if you wish, but each dataset can only access a single record set.

You can however setup multiple datasets and use these concurrently within your report.

Taz

BulkLoad in multiple tables - How to write the annotations?

My Problem: I have to import XML-Files into a complex SQlServer-DB (20
Tables,m2n-relations) via XML Bulkload.
The annotations in the Mapping-Scheme looks like:
--
<sql:relationship name="RLS_LUN" parent="RLS" parent-key="RLS_ID"
child="LUN" child-key="RLS_ID"/>
--
The elements (tablenames) are annotated with:
<xs:element name="LUN" sql:relation="LUN" sql:relationship="RLS_LUN">
--
At executing the Script for Bulkload the following error message appears:
--
Schema: the parent/child table of the relationship on 'DPI' does not match.
--
Some facts:
The tablefields are attributes.
The tables have IDs as identity-columns (must add them as attribute?)
What and where is the problem?
It would be very nice, if someone could help me, it′s important for me,
thank you in advanceThe error you received indicates that the parent and child relations in
your sql:relationship are inconsistent with the relations defined on the
parent and child sides of the relationship.
In addition, the error specifies a relationship not included in your
orginal post (DPI). Posting the entire mapping schema would probably help.
Andrew Conrad
Microsoft Corp|||Now my updated schema (still not working, but why?!?!?):
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2005 sp1 U (http://www.xmlspy.com) by Schlagl
Susanne (-) -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:dt="urn:schemas-microsoft-com:datatypes" elementFormDefault="qualified
"
attributeFormDefault="unqualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="IPI_ISR" parent="IPI" parent-key="IPI_ID"
child="ISR" child-key="IPI_ID"/>
<sql:relationship name="Node_PPI" parent="Node" parent-key="Node_ID"
child="PPI" child-key="Node_ID"/>
<sql:relationship name="PPI_PTT" parent="PPI" parent-key="PPI_ID"
child="PTT" child-key="PPI_ID"/>
<sql:relationship name="PPI_DPT" parent="PPI" parent-key="PPI_ID"
child="DPT" child-key="PPI_ID"/>
<sql:relationship name="Node_ZPI" parent="Node" parent-key="Node_ID"
child="ZPI" child-key="Node_ID"/>
<sql:relationship name="ZPI_DOT" parent="ZPI" parent-key="ZPI_ID"
child="DOT" child-key="ZPI_ID"/>
<sql:relationship name="Node_ORS" parent="Node" parent-key="Node_ID"
child="ORS" child-key="Node_ID"/>
<sql:relationship name="Node_IDS" parent="Node" parent-key="Node_ID"
child="IDS" child-key="Node_ID"/>
<sql:relationship name="IDS_IBS" parent="IDS" parent-key="IDS_ID"
child="IBS" child-key="IDS_ID"/>
<sql:relationship name="Node_RLS" parent="Node" parent-key="Node_ID"
child="RLS" child-key="Node_ID"/>
<sql:relationship name="RLS_LUN" parent="RLS" parent-key="RLS_ID"
child="LUN" child-key="RLS_ID"/>
<sql:relationship name="Node_ORH" parent="Node" parent-key="Node_ID"
child="ORH" child-key="Node_ID"/>
<sql:relationship name="ORH_RHC" parent="ORH" parent-key="ORH_ID"
child="RHC" child-key="ORH_ID"/>
<sql:relationship name="Node_SDS" parent="Node" parent-key="Node_ID"
child="SDS" child-key="Node_ID"/>
<sql:relationship name="Node_ORB" parent="Node" parent-key="Node_ID"
child="ORB" child-key="Node_ID"/>
<sql:relationship name="ORB_RBL" parent="ORB" parent-key="ORB_ID"
child="RBL" child-key="ORB_ID"/>
<sql:relationship name="Node_SST" parent="Node" parent-key="Node_ID"
child="SST" child-key="Node_ID"/>
<sql:relationship name="Node_PSI" parent="Node" parent-key="Node_ID"
child="PSI" child-key="Node_ID"/>
<sql:relationship name="PSI_PRS" parent="PSI" parent-key="PSI_ID"
child="PRS" child-key="PSI_ID"/>
<sql:relationship name="Node_OPS" parent="Node" parent-key="Node_ID"
child="OPS" child-key="Node_ID"/>
<sql:relationship name="OPS_PSR" parent="OPS" parent-key="OPS_ID"
child="PSR" child-key="OPS_ID"/>
<sql:relationship name="Node_LFS" parent="Node" parent-key="Node_ID"
child="LFS" child-key="Node_ID"/>
<sql:relationship name="LFS_ILF" parent="LFS" parent-key="LFS_ID"
child="ILF" child-key="LFS_ID"/>
<sql:relationship name="ILF_LFA" parent="ILF" parent-key="ILF_ID"
child="LFA" child-key="ILF_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="SPR" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="NODE" sql:relation="NODE">
<xs:complexType>
<xs:sequence>
<xs:element name="DPI" sql:relation="DPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="Node" parent-key="Node_ID" child="DPI"
child-key="Node_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="AQS" sql:relation="AQS">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="AQS"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="rqt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rtc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rqs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="AQS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OPI" sql:relation="OPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="OPI"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="did" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="odc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OVE" sql:relation="OVE">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="OVE"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="vid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="vck" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="arn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="vst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="srq" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dck" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OVE_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="TSQ" sql:relation="TSQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="TSQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="xsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsk" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsi" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="TSQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="CPQ" sql:relation="CPQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="CPQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="fci" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="fsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="fps" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="CPQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="WLQ" sql:relation="WLQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="WLQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="wrc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wrt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wrs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="WLQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="BLQ" sql:relation="BLQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="BLQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="ect" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="bec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="BLQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="EXL" sql:relation="EXL">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="EXL"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="nxl" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="EXL_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
<xs:attribute name="tsqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ovec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="blqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="opic" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dpt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wlqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="exlc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cpqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="aqsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
<xs:element name="IPI" sql:relation="IPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="Node" parent-key="Node_ID" child="IPI"
child-key="Node_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="ISR" sql:relation="ISR"
sql:relationship="IPI_ISR">
<xs:complexType>
<xs:attribute name="iss" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="itt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ihp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ipu" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="iku" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="itn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ISR_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="iht" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="IPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PPI" sql:relation="PPI" sql:relationship="Node_PPI">
<xs:complexType>
<xs:sequence>
<xs:element name="DPT" sql:relation="DPT"
sql:relationship="PPI_DPT">
<xs:complexType>
<xs:attribute name="lst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="tts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lsp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pok" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="DPT_ID" type="xs:integer" use="optional"/>
<xs:attribute name="PPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PTT" sql:relation="PTT"
sql:relationship="PPI_PTT">
<xs:complexType>
<xs:attribute name="PTT_ID" type="xs:string" use="optional"/>
<xs:attribute name="PPI_ID" type="xs:string" use="optional"/>
<xs:attribute name="xus" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xok" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xnt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="dkt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ZPI" sql:relation="ZPI" sql:relationship="Node_ZPI">
<xs:complexType>
<xs:sequence>
<xs:element name="DOT" sql:relation="DOT"
sql:relationship="ZPI_DOT">
<xs:complexType>
<xs:attribute name="ook" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ons" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="osp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="onm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ost" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="DOT_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ZPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="okt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ZPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORS" sql:relation="ORS" sql:relationship="Node_ORS">
<xs:complexType>
<xs:sequence>
<xs:element name="ARD" sql:relation="ARD">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="ORS" parent-key="ORS_ID" child="ARD"
child-key="ORS_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="sid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lns" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="mid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lds" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ARD_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="rsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ORS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="IDS" sql:relation="IDS" sql:relationship="Node_IDS">
<xs:complexType>
<xs:sequence>
<xs:element name="IBS" sql:relation="IBS"
sql:relationship="IDS_IBS">
<xs:complexType>
<xs:attribute name="bus" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="bkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="IDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IBS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="IDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IBS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="RLS" sql:relation="RLS" sql:relationship="Node_RLS">
<xs:complexType>
<xs:sequence>
<xs:element name="LUN" sql:relation="LUN"
sql:relationship="RLS_LUN">
<xs:complexType>
<xs:attribute name="lkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LUN_ID" type="xs:integer" use="optional"/>
<xs:attribute name="RLS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="RLS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORH" sql:relation="ORH" sql:relationship="Node_ORH">
<xs:complexType>
<xs:sequence>
<xs:element name="RHC" sql:relation="RHC"
sql:relationship="ORH_RHC">
<xs:complexType>
<xs:attribute name="shn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="RHC_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORH_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ORH_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="SDS" sql:relation="SDS" sql:relationship="Node_SDS">
<xs:complexType>
<xs:attribute name="ddt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sdc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sds" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="SDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORB" sql:relation="ORB" sql:relationship="Node_ORB">
<xs:complexType>
<xs:sequence>
<xs:element name="RBL" sql:relation="RBL"
sql:relationship="ORB_RBL">
<xs:complexType>
<xs:attribute name="rxd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rmd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rdc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="blc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="RBL_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORB_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="rbc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ORB_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="SST" sql:relation="SST" sql:relationship="Node_SST">
<xs:complexType>
<xs:attribute name="ssc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="SST_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PSI" sql:relation="PSI" sql:relationship="Node_PSI">
<xs:complexType>
<xs:sequence>
<xs:element name="PRS" sql:relation="PRS"
sql:relationship="PSI_PRS">
<xs:complexType>
<xs:attribute name="apb" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="mtp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pfs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cap" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="tps" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="upb" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="prc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PRS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="PSI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PSI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OPS" sql:relation="OPS" sql:relationship="Node_OPS">
<xs:complexType>
<xs:sequence>
<xs:element name="PSR" sql:relation="PSR"
sql:relationship="OPS_PSR">
<xs:complexType>
<xs:attribute name="PSR_ID" type="xs:integer" use="optional"/>
<xs:attribute name="OPS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="psrc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OPS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="LFS" sql:relation="LFS" sql:relationship="Node_LFS">
<xs:complexType>
<xs:sequence>
<xs:element name="ILF" sql:relation="ILF"
sql:relationship="LFS_ILF">
<xs:complexType>
<xs:sequence>
<xs:element name="LFA" sql:relation="LFA"
sql:relationship="ILF_LFA">
<xs:complexType>
<xs:attribute name="aec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="txt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LFA_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ILF_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="lfp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lfn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ilc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ILF_ID" type="xs:integer" use="optional"/>
<xs:attribute name="LFS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="lsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LFS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="nty" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rdt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="snm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lrd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rtm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="Node_ID" type="xs:string" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

BulkLoad in multiple tables - How to write the annotations?

My Problem: I have to import XML-Files into a complex SQlServer-DB (20
Tables,m2n-relations) via XML Bulkload.
The annotations in the Mapping-Scheme looks like:
<sql:relationship name="RLS_LUN" parent="RLS" parent-key="RLS_ID"
child="LUN" child-key="RLS_ID"/>
The elements (tablenames) are annotated with:
<xs:element name="LUN" sql:relation="LUN" sql:relationship="RLS_LUN">
At executing the Script for Bulkload the following error message appears:
Schema: the parent/child table of the relationship on 'DPI' does not match.
Some facts:
The tablefields are attributes.
The tables have IDs as identity-columns (must add them as attribute?)
What and where is the problem?
It would be very nice, if someone could help me, it′s important for me,
thank you in advance
The error you received indicates that the parent and child relations in
your sql:relationship are inconsistent with the relations defined on the
parent and child sides of the relationship.
In addition, the error specifies a relationship not included in your
orginal post (DPI). Posting the entire mapping schema would probably help.
Andrew Conrad
Microsoft Corp
|||Now my updated schema (still not working, but why?!?!?):
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2005 sp1 U (http://www.xmlspy.com) by Schlagl
Susanne (-) -->
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:dt="urn:schemas-microsoft-com:datatypes" elementFormDefault="qualified"
attributeFormDefault="unqualified">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="IPI_ISR" parent="IPI" parent-key="IPI_ID"
child="ISR" child-key="IPI_ID"/>
<sql:relationship name="Node_PPI" parent="Node" parent-key="Node_ID"
child="PPI" child-key="Node_ID"/>
<sql:relationship name="PPI_PTT" parent="PPI" parent-key="PPI_ID"
child="PTT" child-key="PPI_ID"/>
<sql:relationship name="PPI_DPT" parent="PPI" parent-key="PPI_ID"
child="DPT" child-key="PPI_ID"/>
<sql:relationship name="Node_ZPI" parent="Node" parent-key="Node_ID"
child="ZPI" child-key="Node_ID"/>
<sql:relationship name="ZPI_DOT" parent="ZPI" parent-key="ZPI_ID"
child="DOT" child-key="ZPI_ID"/>
<sql:relationship name="Node_ORS" parent="Node" parent-key="Node_ID"
child="ORS" child-key="Node_ID"/>
<sql:relationship name="Node_IDS" parent="Node" parent-key="Node_ID"
child="IDS" child-key="Node_ID"/>
<sql:relationship name="IDS_IBS" parent="IDS" parent-key="IDS_ID"
child="IBS" child-key="IDS_ID"/>
<sql:relationship name="Node_RLS" parent="Node" parent-key="Node_ID"
child="RLS" child-key="Node_ID"/>
<sql:relationship name="RLS_LUN" parent="RLS" parent-key="RLS_ID"
child="LUN" child-key="RLS_ID"/>
<sql:relationship name="Node_ORH" parent="Node" parent-key="Node_ID"
child="ORH" child-key="Node_ID"/>
<sql:relationship name="ORH_RHC" parent="ORH" parent-key="ORH_ID"
child="RHC" child-key="ORH_ID"/>
<sql:relationship name="Node_SDS" parent="Node" parent-key="Node_ID"
child="SDS" child-key="Node_ID"/>
<sql:relationship name="Node_ORB" parent="Node" parent-key="Node_ID"
child="ORB" child-key="Node_ID"/>
<sql:relationship name="ORB_RBL" parent="ORB" parent-key="ORB_ID"
child="RBL" child-key="ORB_ID"/>
<sql:relationship name="Node_SST" parent="Node" parent-key="Node_ID"
child="SST" child-key="Node_ID"/>
<sql:relationship name="Node_PSI" parent="Node" parent-key="Node_ID"
child="PSI" child-key="Node_ID"/>
<sql:relationship name="PSI_PRS" parent="PSI" parent-key="PSI_ID"
child="PRS" child-key="PSI_ID"/>
<sql:relationship name="Node_OPS" parent="Node" parent-key="Node_ID"
child="OPS" child-key="Node_ID"/>
<sql:relationship name="OPS_PSR" parent="OPS" parent-key="OPS_ID"
child="PSR" child-key="OPS_ID"/>
<sql:relationship name="Node_LFS" parent="Node" parent-key="Node_ID"
child="LFS" child-key="Node_ID"/>
<sql:relationship name="LFS_ILF" parent="LFS" parent-key="LFS_ID"
child="ILF" child-key="LFS_ID"/>
<sql:relationship name="ILF_LFA" parent="ILF" parent-key="ILF_ID"
child="LFA" child-key="ILF_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:element name="SPR" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="NODE" sql:relation="NODE">
<xs:complexType>
<xs:sequence>
<xs:element name="DPI" sql:relation="DPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="Node" parent-key="Node_ID" child="DPI"
child-key="Node_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="AQS" sql:relation="AQS">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="AQS"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="rqt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rtc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rqs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="AQS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OPI" sql:relation="OPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="OPI"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="did" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="odc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OVE" sql:relation="OVE">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="OVE"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="vid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="vck" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="arn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="vst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="srq" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dck" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OVE_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="TSQ" sql:relation="TSQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="TSQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="xsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsk" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsi" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="TSQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="CPQ" sql:relation="CPQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="CPQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="fci" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="fsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="fps" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="CPQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="WLQ" sql:relation="WLQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="WLQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="wrc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wrt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wrs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="WLQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="BLQ" sql:relation="BLQ">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="BLQ"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="ect" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="bec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="BLQ_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="EXL" sql:relation="EXL">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="DPI" parent-key="DPI_ID" child="EXL"
child-key="DPI_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="nxl" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="EXL_ID" type="xs:integer" use="optional"/>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="DPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
<xs:attribute name="tsqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ovec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="blqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="opic" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dpt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="wlqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="exlc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cpqc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="aqsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
<xs:element name="IPI" sql:relation="IPI">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="Node" parent-key="Node_ID" child="IPI"
child-key="Node_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="ISR" sql:relation="ISR"
sql:relationship="IPI_ISR">
<xs:complexType>
<xs:attribute name="iss" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="itt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ihp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ipu" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="iku" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="itn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ISR_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="iht" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="IPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PPI" sql:relation="PPI" sql:relationship="Node_PPI">
<xs:complexType>
<xs:sequence>
<xs:element name="DPT" sql:relation="DPT"
sql:relationship="PPI_DPT">
<xs:complexType>
<xs:attribute name="lst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="tts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lsp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pok" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="DPT_ID" type="xs:integer" use="optional"/>
<xs:attribute name="PPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PTT" sql:relation="PTT"
sql:relationship="PPI_PTT">
<xs:complexType>
<xs:attribute name="PTT_ID" type="xs:string" use="optional"/>
<xs:attribute name="PPI_ID" type="xs:string" use="optional"/>
<xs:attribute name="xus" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xok" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="xnt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="dkt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ZPI" sql:relation="ZPI" sql:relationship="Node_ZPI">
<xs:complexType>
<xs:sequence>
<xs:element name="DOT" sql:relation="DOT"
sql:relationship="ZPI_DOT">
<xs:complexType>
<xs:attribute name="ook" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ons" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="osp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="onm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sts" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ost" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="DOT_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ZPI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="okt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ZPI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORS" sql:relation="ORS" sql:relationship="Node_ORS">
<xs:complexType>
<xs:sequence>
<xs:element name="ARD" sql:relation="ARD">
<xs:annotation>
<xs:appinfo>
<sql:relationship parent="ORS" parent-key="ORS_ID" child="ARD"
child-key="ORS_ID"/>
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="sid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lns" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="mid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lds" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ARD_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="rsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ORS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="IDS" sql:relation="IDS" sql:relationship="Node_IDS">
<xs:complexType>
<xs:sequence>
<xs:element name="IBS" sql:relation="IBS"
sql:relationship="IDS_IBS">
<xs:complexType>
<xs:attribute name="bus" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="bkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="IDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IBS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="IDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="IBS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="RLS" sql:relation="RLS" sql:relationship="Node_RLS">
<xs:complexType>
<xs:sequence>
<xs:element name="LUN" sql:relation="LUN"
sql:relationship="RLS_LUN">
<xs:complexType>
<xs:attribute name="lkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LUN_ID" type="xs:integer" use="optional"/>
<xs:attribute name="RLS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="RLS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORH" sql:relation="ORH" sql:relationship="Node_ORH">
<xs:complexType>
<xs:sequence>
<xs:element name="RHC" sql:relation="RHC"
sql:relationship="ORH_RHC">
<xs:complexType>
<xs:attribute name="shn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rst" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="RHC_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORH_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ORH_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="SDS" sql:relation="SDS" sql:relationship="Node_SDS">
<xs:complexType>
<xs:attribute name="ddt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sdc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="dsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sds" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="SDS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="ORB" sql:relation="ORB" sql:relationship="Node_ORB">
<xs:complexType>
<xs:sequence>
<xs:element name="RBL" sql:relation="RBL"
sql:relationship="ORB_RBL">
<xs:complexType>
<xs:attribute name="rxd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rmd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rdc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="blc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="RBL_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ORB_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="rbc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ORB_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="SST" sql:relation="SST" sql:relationship="Node_SST">
<xs:complexType>
<xs:attribute name="ssc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="SST_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="PSI" sql:relation="PSI" sql:relationship="Node_PSI">
<xs:complexType>
<xs:sequence>
<xs:element name="PRS" sql:relation="PRS"
sql:relationship="PSI_PRS">
<xs:complexType>
<xs:attribute name="apb" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="mtp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pfs" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="cap" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="tps" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="upb" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="prc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PRS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="PSI_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="PSI_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="OPS" sql:relation="OPS" sql:relationship="Node_OPS">
<xs:complexType>
<xs:sequence>
<xs:element name="PSR" sql:relation="PSR"
sql:relationship="OPS_PSR">
<xs:complexType>
<xs:attribute name="PSR_ID" type="xs:integer" use="optional"/>
<xs:attribute name="OPS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="psrc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pnm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="pkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="psc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="OPS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
<xs:element name="LFS" sql:relation="LFS" sql:relationship="Node_LFS">
<xs:complexType>
<xs:sequence>
<xs:element name="ILF" sql:relation="ILF"
sql:relationship="LFS_ILF">
<xs:complexType>
<xs:sequence>
<xs:element name="LFA" sql:relation="LFA"
sql:relationship="ILF_LFA">
<xs:complexType>
<xs:attribute name="aec" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="txt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LFA_ID" type="xs:integer" use="optional"/>
<xs:attribute name="ILF_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="lfp" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lfn" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lkw" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ilc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="ILF_ID" type="xs:integer" use="optional"/>
<xs:attribute name="LFS_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="lsc" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="LFS_ID" type="xs:integer" use="optional"/>
<xs:attribute name="Node_ID" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="nty" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rdt" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="sid" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="snm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="lrd" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="rtm" type="xs:string" use="required"
sql:datatype="nvarchar(255)"/>
<xs:attribute name="Node_ID" type="xs:string" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Bulkload copying identity columns within a table

The database I must insert my data into is rather old, and poorly designed.
We have a situation were a table can be the child of many tables; the "key"
in the child can reference more than one parent. I have created a simple
example that gets at the heart of my problem; how can I copy an identity
column from the parent into more than one column in the child.
The sample below produces:
Parent
1TestData
Child
1 1 0 DataTest -> I need 1 1 1 DataTest
Using SQL 2000, SQLXML 3.0 SP3. Table, Schema and XML:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
GO
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
GO
<?xml version="1.0" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<!-- This double copy does not work -->
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID"
child="Child"
child-key="ParentID ParentID_B" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="Parent" sql:relation="Parent">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ParentID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Foo" type="xsd:string" />
<xsd:element name="Child" sql:relation="Child"
sql:relationship="Children">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ChildID" type="xsd:decimal" sql:identity="ignore" />
<xsd:element name="Bar" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
Thanks in advance.
I also discovered that if I extend my sample data another row; 1 more parent
and child, that the subsequent values of ParentID_B increase based on the
number of parent's. Maybe I do not understand some mechanics going on behind
the scenes. Clearly there is a pattern here.
The XML below produces
ParentID Foo
-- --
1 TestData
2 TestData
ChildID ParentID ParentID_B Bar
-- -- -- --
1 1 0 DataTest
2 2 1 DataTest
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
<Parent>
<Foo>TestData</Foo>
<Child>
<Bar>DataTest</Bar>
</Child>
</Parent>
</ROOT>
|||What is the exactly question here? It looks like your previous post is
deleted.
What is the column type for ParentID_B?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>I also discovered that if I extend my sample data another row; 1 more
>parent
> and child, that the subsequent values of ParentID_B increase based on the
> number of parent's. Maybe I do not understand some mechanics going on
> behind
> the scenes. Clearly there is a pattern here.
> The XML below produces
> ParentID Foo
> -- --
> 1 TestData
> 2 TestData
> ChildID ParentID ParentID_B Bar
> -- -- -- --
> 1 1 0 DataTest
> 2 2 1 DataTest
>
> <?xml version="1.0" encoding="utf-8" ?>
> <ROOT>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> <Parent>
> <Foo>TestData</Foo>
> <Child>
> <Bar>DataTest</Bar>
> </Child>
> </Parent>
> </ROOT>
|||My original post described my problem, but the second post was to describe
some new information I had learned after playing with the code for a day. The
original post is still valid, it contains a table schema, xsd and xml
document.
However, I will try and re-describe this:
Imagine two tables, one called "Parent" and one called "Child". The schema
for these tables is as follows:
CREATE TABLE Parent (
ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
Foo VarChar(10) NULL
)
CREATE TABLE Child (
ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
ParentID Int NOT NULL,
ParentID_B Int NOT NULL,
Bar VarChar(10) NULL
)
In "Child" the value of "ParentID" and "ParentID_B" should be the same; they
need to be populated with the value from Parent.ParentID after the insert.
(See the xsd in the original post)
I thought I could use a relationship like this to populate both columns when
using SQLXML:
<sql:relationship name="Children"
parent="Parent"
parent-key="ParentID ParentID" <-This is copied more than once (does not
work)
child="Child"
child-key="ParentID ParentID_B" />
However, what I find is that the value of ParentID_B will be zero in the
first child, 1 in the second, 2 in the third etc. In other words, ParentID
will get the correct value, but ParentID_B will always be one less than
ParentID.
Since my relationship above does not work, how can I copy the values for
subsequent children of “Parent” and have both ParentID and ParentID_B be the
same value from the table "Parent"?
This example is heavily oversimplified, my real database is much more
complex, but this shows the mechanics of my problem more clearly I believe.
"Bertan ARI [MSFT]" wrote:

> What is the exactly question here? It looks like your previous post is
> deleted.
> What is the column type for ParentID_B?
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:28CD7435-F464-45EE-94F0-738553980AC7@.microsoft.com...
>
>
|||This looks like a bug in our code. Thanks for reporting it
Unfortunately, I don't know any simple workaround for now. Is it possible
for you to create a script that will update ParentID_B columns after
bulkload?
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:FEEBE11C-8B69-43CF-9635-B473A36CE22A@.microsoft.com...[vbcol=seagreen]
> My original post described my problem, but the second post was to describe
> some new information I had learned after playing with the code for a day.
> The
> original post is still valid, it contains a table schema, xsd and xml
> document.
> However, I will try and re-describe this:
> Imagine two tables, one called "Parent" and one called "Child". The schema
> for these tables is as follows:
> CREATE TABLE Parent (
> ParentID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> Foo VarChar(10) NULL
> )
> CREATE TABLE Child (
> ChildID Int NOT NULL PRIMARY KEY IDENTITY(1,1),
> ParentID Int NOT NULL,
> ParentID_B Int NOT NULL,
> Bar VarChar(10) NULL
> )
> In "Child" the value of "ParentID" and "ParentID_B" should be the same;
> they
> need to be populated with the value from Parent.ParentID after the insert.
> (See the xsd in the original post)
> I thought I could use a relationship like this to populate both columns
> when
> using SQLXML:
> <sql:relationship name="Children"
> parent="Parent"
> parent-key="ParentID ParentID" <-This is copied more than once (does not
> work)
> child="Child"
> child-key="ParentID ParentID_B" />
> However, what I find is that the value of ParentID_B will be zero in the
> first child, 1 in the second, 2 in the third etc. In other words, ParentID
> will get the correct value, but ParentID_B will always be one less than
> ParentID.
> Since my relationship above does not work, how can I copy the values for
> subsequent children of "Parent" and have both ParentID and ParentID_B be
> the
> same value from the table "Parent"?
> This example is heavily oversimplified, my real database is much more
> complex, but this shows the mechanics of my problem more clearly I
> believe.
>
> "Bertan ARI [MSFT]" wrote: