December 14, 2010 at 9:51 am
--Create Individual Table
CREATE
TABLE [dbo].[Individual]
(
[Individual_ID] [int]
IDENTITY(1,1) primary key clustered,
[InternalID] [int]
NULL,
[Action] [nvarchar]
(255) NULL,
[InsertUserID] [nvarchar]
(255) NULL,
[InsertDateTime] [nvarchar]
(255) NULL,
[UpdateUserID] [nvarchar]
(255) NULL
)
--Create Appointment Table
CREATE
TABLE [dbo].[Appointment]
(
[Appointment_ID] [int]
IDENTITY(1,1) primary key clustered,
[Individual_ID]
int references Individual(Individual_ID),
[InternalID] [int]
NULL,
[Action] [nvarchar]
(255) NULL,
[InsertUserID] [nvarchar]
(255) NULL,
[InsertDateTime] [nvarchar]
(255) NULL,
[UpdateUserID] [nvarchar]
(255) NULL
)
--Create RequestTable
CREATE
TABLE [dbo].[Request]
(
[Request_ID] [int]
IDENTITY(1,1) primary key clustered,
[Appointment_ID]
int references Appointment(Appointment_ID),
[InternalID] [int]
NULL,
[Action] [nvarchar]
(255) NULL,
[InsertUserID] [nvarchar]
(255) NULL,
[InsertDateTime] [nvarchar]
(255) NULL,
[UpdateUserID] [nvarchar]
(255) NULL
)
DECLARE
@hDoc int
EXEC
sp_xml_preparedocument @hDoc OUTPUT,
'<Individual InternalID="154805" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>08-29-2008 17:13:57</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>13-05-2010 13:22:41</UpdateDateTime>
<Appointments>
<Appointment InternalID="4926250" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Requests>
<Request InternalID="3406436" Action="None">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Request>
</Requests>
</Appointment>
<Appointment InternalID="4926251" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>08-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Requests>
<Request InternalID="3406437" Action="None">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Request>
</Requests>
</Appointment>
</Appointments>
</Individual>'
-----Insert into Individual Table
Insert
into Individual
SELECT
*
FROM
OPENXML(@hDoc, '/Individual',3)
WITH
(
[InternalID] [int]
,
[Action]
varchar(10),
[InsertUserID]
varchar(10),
[InsertDateTime]
varchar(10),
[UpdateUserID]
varchar(10))
Declare
@Individual_ID INT
SELECT
@Individual_ID = @@IDENTITY
--Insert into Appointment table
Insert
into Appointment
select
@Individual_ID,*
FROM
OPENXML(@hDoc, '/Individual/Appointments/Appointment',3)
WITH
(
[InternalID] [int]
,
[Action]
varchar(10),
[InsertUserID]
varchar(10),
[InsertDateTime]
varchar(10),
[UpdateUserID]
varchar(10))
Declare
@Appointment_ID int
select
@Appointment_ID = @@IDENTITY
--Insert into Request table
Insert
into request
select
@Appointment_ID,*
FROM
OPENXML(@hDoc, '/Individual/Appointments/Appointment/Requests/Request',3)
WITH
(
[InternalID] [int]
,
[Action]
varchar(10),
[InsertUserID]
varchar(10),
[InsertDateTime]
varchar(10),
[UpdateUserID]
varchar(10))
------------------------------------------------------
select
* from Individual
select
* from Appointment
select
* from Request
--------------------------------
Above is the code what i am doing,,,,,
In the request table ,,for the appointment_ID,,I need the appropriate Identity from the Appointment table.
Instead of I am getting the last identity value for both the row in the Request table,,,
I am new in XML,,any expert can help me to how to loop through XML file,,i tried cursor but its not working,,
December 14, 2010 at 10:07 am
Use XQuery to shred the XML, and use an OUTPUT clause to get the IDs from the insert.
You'll start with this:
DECLARE @XML XML;
SELECT @XML = '<Individual InternalID="154805" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>08-29-2008 17:13:57</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>13-05-2010 13:22:41</UpdateDateTime>
<Appointments>
<Appointment InternalID="4926250" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Requests>
<Request InternalID="3406436" Action="None">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Request>
</Requests>
</Appointment>
<Appointment InternalID="4926251" Action="Update">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>08-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>SYSTEM_USER</UpdateUserID>
<UpdateDateTime>11-05-2009 15:22:44</UpdateDateTime>
<Requests>
<Request InternalID="3406437" Action="None">
<InsertUserID>ASDZXCCFG</InsertUserID>
<InsertDateTime>02-20-2008 16:27:54</InsertDateTime>
<UpdateUserID>CATS</UpdateUserID>
<UpdateDateTime>02-22-2008 12:12:43</UpdateDateTime>
</Request>
</Requests>
</Appointment>
</Appointments>
</Individual>';
SELECT Indiv.Appt.query('.')
FROM @XML.nodes('/Individual/Appointments') AS Indiv(Appt);
You'll need to use the "value()" function to get the various values.
That part will look like this:
SELECT Indiv.Appt.query('.'),
Indiv.Appt.query('.').value('(Appointments/Appointment/@InternalID)[1]','int')
FROM @XML.nodes('/Individual/Appointments') AS Indiv(Appt);
That will give you the data in a tabular format, which can be easily inserted into a table. Then use the "OUTPUT INTO" option to get the ID values from that table. MSDN has the details on how that works. Bing/Google "t-sql output" and it should be the top result.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2010 at 10:33 am
Hey thanks for your really fast reply,,,
I need my request table something like that,,
Request_ID Appointment_ID InternalID Action
1 1 4926251 None
2 2 3406437 None
I need the Identity column value of the Appointment table into Request table,,
I don't know how to process one row from appointment and get that identity and insert it into the request table and for the second row in the appoitment table and get the identity value from that and insert it into the second inserted row in the request table.
December 14, 2010 at 11:10 am
For that, you'd use a cursor. But there's no reason to. You can use Output Into to insert all the rows at once, then get all the IDs at once, and insert into the second table one time.
It looks like:
insert into dbo.My1stTable (Col1, Col2)
output inserted.ID, inserted.Col2 into dbo.MySecondTable (ColA, ColB)
from @XML.nodes(...);
If the second table has constraints on it, like a foriegn key to the first table, you have to Output Into a temp table, then use that to insert into the second table.
That will do it all at one time, or in two steps, no matter how many rows you need to insert.
If you really want to do it one row at a time (it's slower, less efficient and more error-prone), then use a cursor to step through each row in the XML, using XQuery to parse the XML into rows, and then run the cursor to insert into each table one row at a time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2011 at 3:55 am
But im getting null values for last node
Please help me out ,its urgent
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply