Update Tables in Stored Procedure [RESOLVED]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Tables in Stored Procedure [RESOLVED]

Author

Topic

Talsiter
Starting Member

3 Posts

Posted - 2006-04-08 : 17:48:17

I have asked this question in other forms and am unable to get a response. Not sure if this can be done or not. What I need to do is generate a reference to the parent table from the child table using XML. If I am going about this the wrong way please let me know.

I am having so many problems with seaming less tasks. I am very new to SQL so be gentle. I found this code on the net and attempted to modify it so I could us it. I can’t get it to work.

Expected Result:

Table1

MyT1ID || FName || LName
1 || Joe || Hester
2 || Fred || Tally

Table 2
ID || fk_MyT1ID || Make || Model
1 || 1 || Dodge || Caravan
2 || 1 || Ford || Mustang
3 || 1 || Ford || Tempo
4 || 2 || Pontiac || Grand AM

My Result:
MyT1ID || FName || LName
1 || Joe || Hester
2 || Fred || Tally

Table 2:

ID || fk_MyT1ID || Make || Model
1 || 1 || Dodge || Caravan
2 || 2 || Pontiac || Grand AM

Anyone have any ideas? Not sure if it is the XML or Stored Procedure

Sample Code:

use tempdb
go
CREATE TABLE MyT1(MyT1ID int IDENTITY(1,1) NOT NULL, FName nchar(50), LName nchar(50),
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(MyT1ID ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
go
CREATE TABLE MyT2(ID int IDENTITY(1,1) NOT NULL, fk_MyT1ID int NOT NULL, Make nchar(50) ,Model nchar(50),
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
([ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
go

DECLARE @xmldoc varchar(8000)
set @xmldoc =
'<MyData>
<Owner>
<FName>Joe</FName>
<LName>Hester</LName>
<Vehicle>
<Make>Dodge</Make>
<Model>Caravan</Model>
</Vehicle>
<Vehicle>
<Make>Ford</Make>
<Model>Mustang</Model>
</Vehicle>
<Vehicle>
<Make>Ford</Make>
<Model>Tempo</Model>
</Vehicle>
</Owner>
<Owner>
<FName>Fred</FName>
<LName>Tally</LName>
<Vehicle>
<Make>Pontiac</Make>
<Model>Grand AM</Model>
</Vehicle>
</Owner>
</MyData>
'
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldoc

INSERT MyT1 (FName, LName)
SELECT FName, LName
FROM OPENXML (@hdoc, '/MyData/Owner')
with (FName char(10) 'FName', LName char(10) 'LName')

INSERT MyT2 (Make, fk_MyT1ID, Model)
SELECT x.Make, MyT1.MyT1ID, Model
FROM
(SELECT T2Data, Make, Model
FROM OPENXML (@hdoc, '/MyData/Owner')
with (
T2Data char(10) 'FName',
Make char(10) 'Vehicle/Make',
Model char(10) 'Vehicle/Model')) x (T2Data, Make, Model)
JOIN MyT1 ON MyT1.FName = x.T2Data

EXEC sp_xml_removedocument @hdoc
go

SELECT *
FROM MyT1
go

SELECT *
FROM MyT2
go

DROP TABLE MyT2
DROP TABLE MyT1

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-08 : 18:19:36

INSERT MyT2 (Make, fk_MyT1ID, Model)
SELECT x.Make, MyT1.MyT1ID, Model
FROM
(SELECT T2Data, Make, Model
FROM OPENXML (@hdoc, '/MyData/Owner/Vehicle')
with (
T2Data char(10) '../FName',
Make char(10) 'Make',
Model char(10) 'Model')) x (T2Data, Make, Model)
JOIN MyT1 ON MyT1.FName = x.T2Data

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Talsiter
Starting Member

3 Posts

Posted - 2006-04-09 : 08:21:28

nr, Thank you VERY MUCH for your response. If I apply the response that was given the code will work as expected. The problem is that if I add another record to the XML for example:

<Owner>
<FName>Joe</FName>
<LName>Wright</LName>
<Vehicle>
<Make>Geo</Make>
<Model>Metro</Model>
</Vehicle>
</Owner>

I get a unexpected result. It doubles all the vehicles. I think this is due to it being joined on the FName. Is there a hidden index or something that I can reference in the XML? If not, I will begin the research on the “Cursors or DTS”

Again thank you very much for your response.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-09 : 08:42:17

The problem is that you now have a duplicate FName in the data. You need to join on unique fields which looks like it is FName, LName. You should also place a unique index on these columns.
You would probably find it easier to insert the data into a tmp table then insert to the production tables from there - just get the data from the subquery in the second insert - you will then find it a lot easier to see what is going on.
A cursor or dts would not help here as it is a data and design problem. For tyhe data you have now given - but you need to look at the underlying structure to see if this is the complete solution:

INSERT MyT2 (Make, fk_MyT1ID, Model)
SELECT x.Make, MyT1.MyT1ID, Model
FROM
(SELECT FName, LName, Make, Model
FROM OPENXML (@hdoc, '/MyData/Owner/Vehicle')
with (
FName char(10) '../FName',
LName char(10) '../LName',
Make char(10) 'Make',
Model char(10) 'Model')) x (T2Data, Make, Model)
JOIN MyT1
ON MyT1.FName = x.FName
and MyT1.LName = x.LName

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Talsiter
Starting Member

3 Posts

Posted - 2006-04-09 : 17:37:40

Thank you so very much, this looks like it is going to work. Your help is greatly appreciated. I have been looking for the answer for several weeks now.
Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources