Saving data transferred as XML string

Question

Saving data transferred as XML string

Zahid-285296

SSC Rookie

Points: 43
More actions
December 12, 2005 at 5:09 am

#166445

Hello All,
I m trying to save some data that is being passed to this stored procedure in as a XML string. Here is the SP code :
CREATE PROCEDURE SP_AddScreenXML
@strXML ntext
AS
declare @hdoc int
exec sp_xml_preparedocument @hdoc OUTPUT,@strXML
insert into tblScreensRoles
select * from openxml(@hdoc,'/root/screens',2)
with (ScreenId int, RoleId int)
exec sp_xml_removedocument @hdoc
return
GO
It runs without any error but the problem i m facing is it only saves first record in the string and ignores all the others. say if there are 4 records passed in the string , it only saves 1st fomr the list. The xml string being passed looks like this :
<root>
<screens>
<ScreenId>1</ScreenId><RoleId>1</RoleId>
<ScreenId>3</ScreenId><RoleId>1</RoleId>
<ScreenId>7</ScreenId><RoleId>1</RoleId>
<ScreenId>8</ScreenId><RoleId>1</RoleId>
</screens>
</root>

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

I think you should consider changing your XML a bit. As things are now, it is hard to connect screenid and roleid. Instead try the following (where related screenid's and roleid's are enclosed in the same screen-tag).

CREATE PROCEDURE SP_AddScreenXML

@strXML ntext

AS

declare @hdoc int

exec sp_xml_preparedocument @hdoc OUTPUT,@strXML

--insert into tblScreensRoles

select * from openxml(@hdoc,'/root/screens/screen',2)

with (ScreenId int, RoleId int)

exec sp_xml_removedocument @hdoc

return

GO

exec SP_AddScreenXML '<root><screens><screen><ScreenId>1</ScreenId><RoleId>1</RoleId></screen><screen><ScreenId>3</ScreenId><RoleId>1</RoleId></screen><screen><ScreenId>7</ScreenId><RoleId>1</RoleId></screen><screen><ScreenId>8</ScreenId><RoleId>1</RoleId></screen></screens></root>'

drop proc SP_AddScreenXML

go

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 2

Been thinking some more....

If you insist on the XML format (which I still think you shouldn't), I believe that the following will work:

CREATE PROCEDURE SP_AddScreenXML

@strXML ntext

AS

declare @hdoc int

exec sp_xml_preparedocument @hdoc OUTPUT, @strXML

declare @screens table(Id int identity(1, 1), ScreenId varchar(100))

declare @roles table(Id int identity(1, 1), RoleId varchar(100))

insert @screens (ScreenId)

select cast(text as varchar(100))

from openxml(@hdoc,'/root/screens/ScreenId',2)

where nodetype = 3

insert @roles (RoleId)

select cast(text as varchar(100))

from openxml(@hdoc,'/root/screens/RoleId',2)

where nodetype = 3

select ScreenId, RoleId from @Screens s inner join @Roles r on s.Id = r.Id

exec sp_xml_removedocument @hdoc

return

GO

exec SP_AddScreenXML '<root><screens><ScreenId>1</ScreenId><RoleId>1</RoleId><ScreenId>3</ScreenId><RoleId>1</RoleId><ScreenId>7</ScreenId><RoleId>1</RoleId><ScreenId>8</ScreenId><RoleId>1</RoleId></screens></root>'

drop proc SP_AddScreenXML

go

Zahid-285296 SSC Rookie Points: 43 More actions · Answer 3

Thanks Jesper... it worked !! .. i appreciate your help.

That was the first time i used XML to save data using SP.. Can you please elaborate a bit why we had to do it the way you suggested ?

it'll be of great help for me..

Thanks again..

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

You need to stress which ScreenId's and RoleId's are connected. In your first XML, you have 4 ScreenId's and 4 RoleId's describing one Screens entity, with no clear relation between corresponding ScreenId's and RoleId's. You should probably have 1 ScreenId and 1 RoleId describing each Screen, and a list of Screens describing all screens, as in the XML I suggested.

This is based on my understanding of your data, and other people may have different opinions...