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:

Table 1

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

Talsiter:

The main issue is that when you specified the /MyData/Owner in the OPENXML clause it will only return one record per owner. So you need to change it to /MyData/Owner/ and then modify the with clause to point to the correct level. Look below:

SELECT T2Data, Make, Model
FROM OPENXML (@hdoc, '/MyData/Owner/Vehicle')
with ( T2Data char(10) '../FName',
        Make char(10) 'Make',
        Model char(10) 'Model')

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com