Buk Insert through Xml - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Buk Insert through Xml

Author

Topic

gaurav.five
Starting Member

2 Posts

Posted - 2008-12-17 : 01:34:32

Hello Friends,

This is Gaurav,I am havin an small problem I have to in sert bulk data into 2 tables one is USER and another is Records table
1. USER table having fields as
UserId
Password
RecordId foreign key

2. RECORDS table having fields as
RecordId Auto generated number abd Primary key
AttributeId
RecordValue

I have to insert data into these table in bulk at the same time
my problem is that how i will get th RecorID to insert into User table.I am Writing the Stored Procedure for this. I have to first insert into table Records and then into the User table and I am sending an Xml to the Stored Procedure that Xmal reads the value and inserts tha data but my problem is that how i willget the RecordId
that I want to insert into the User Table.Because RecorId is autogenerated number and it is the Foreign Key in the User Table.
how can i perform this by Sql Query

My Query is

INSERT Into EntityRecords(AttributeSetId,IdentityValue,RecordValue)
output inserted.RecordId into @User(Id)
SELECT AttributeSetId,IdentityValue,RecordValue From OPENXML(@hDoc,'/User/UserRecord',1)
With(AttributeSetId int '@AttributeSetId',IdentityValue varbinary(1024) '@IdentityValue',
RecordValue varbinary(Max) '@RecordValue')
into ExternalUser(UserId,DirectoryId,Password)
SELECT UserId,DirectoryId,Password From OPENXML(@hDoc,'/User/UserRecord',1)
With(UserId varchar(256) '@UserId',DirectoryId int '@DirectoryId',
Password nvarchar(128) '@Password')

this is giving Error

Gaurav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 01:51:40

[code]
CREATE PROCEDURE UpdateXMLdata
@YourXML xml
AS
DECLARE @INSERTED_VALUES table
(
RecordId int,
AttributeSetId int,
IdentityValue varbinary(1024),
RecordValue varbinary(Max)
)

INSERT Into EntityRecords(AttributeSetId,IdentityValue,RecordValue)
OUTPUT INSERTED.RecordId,INSERTED.AttributeSetId,INSERTED.IdentityValue,INSERTED.RecordValue INTO @INSERTED_VALUES
SELECT t.u.value('.[1]/@AttributeSetId','int'),
t.u.value('.[1]/@IdentityValue','varbinary(1024)'),
t.u.value('.[1]/@RecordValue','varbinary(Max)')
FROM @YourXML.nodes('/User/UserRecord')t(u)

INSERT INTO Users(UserId,Password,RecordId)
SELECT t.UserId,t. Password,i.RecordId
FROM @INSERTED_VALUES i
JOIN
(
SELECT t.u.value('.[1]/@AttributeSetId','int') AS AttributeSetId,
t.u.value('.[1]/@IdentityValue','varbinary(1024)') AS IdentityValue,
t.u.value('.[1]/@RecordValue','varbinary(Max)') AS RecordValue,
t.u.value('.[1]/@UserId','varchar(256)') AS UserId,
t.u.value('.[1]/@Password','nvarchar(128)') AS Password
FROM @YourXML.nodes('/User/UserRecord')t(u)
)t
ON t.AttributeSetId=i.AttributeSetId
AND t.IdentityValue=i.IdentityValue
AND t.RecordValue=i.RecordValue
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources