Saving XML data using stored procedure

• Zahid-285296

  SSC Rookie

  Points: 33

  More actions

  December 12, 2005 at 5:23 am

  #67115

  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>

• Bert De Haes

  Hall of Fame

  Points: 3078

  More actions

  December 13, 2005 at 12:53 am

  #609141

  For this to work, your xml should be like :

  <root>

  <screens>

  <ScreenId>1</ScreenId><RoleId>1</RoleId>

  </screens>

  <screens>

  <ScreenId>3</ScreenId><RoleId>1</RoleId>

  </screens>

  <screens>

  <ScreenId>7</ScreenId><RoleId>1</RoleId>

  </screens>

  <screens>

  <ScreenId>8</ScreenId><RoleId>1</RoleId>

  </screens>

  </root>