Using XML Data Type in SQL Server 2005...

• Saravanan Krishnan

  Grasshopper

  Points: 15

  More actions

  March 26, 2008 at 11:26 pm

  #71727

  Hi, I'm new to SQLSERVERCENTRAL and SQL2005 with respect to XML data type.

  I'm developing a C# Windows application, i'm using 2 columns 1st is Id(pk type:int) 2nd is a column of type xml in my db(sqlserver2k5) table. I'm able send the xml element along with 4 attributes in it (as a string type) to my table's xml typed column. Now what i actually need to do is instead of using 2 columns i want 5 columns [like Id, Itemid, Desc, Partno, Modelno] in my table and when i pass my string(xml element with 4 attributes) the attributes within the string has to be assigned to 4 columns in the table.

  eg: This is how my string (XML Element) i pass looks like

  "[ITEM itemid="001" desc="pc" partno="245" modelno="7ehg452"/]"

  Any help is Appreciated...

  Thanks in Advance

  Saravanan krishnan.

• Srinivas Chilakapati

  SSC Enthusiast

  Points: 123

  More actions

  March 27, 2008 at 3:08 am

  #794483

  Hi

  Here is a sample code which creates a stored procedure and executes the xml against that in query analyzer. Just follow the steps and modify the code as it suits to you once u throogh the output.

  1) Create Procedure...

  CREATE PROCEDURE [dbo].[Study_SaveData](

  @studyDataXML XML

  )

  AS

  BEGIN

  SELECT StudyTab.StudyCol.value('StudyParID[1]','int') AS StudyParID,

  StudyTab.StudyCol.value('TPID[1]','int') AS TPID,

  StudyTab.StudyCol.value('DataValue[1]','float') AS DataValue

  FROM @studyDataXML.nodes('//StudyDataList/StudyData') AS StudyTab(StudyCol)

  END

  2) Run the procedure like this.......

  EXEC [dbo].[Study_SaveData]

  '

  '

  bingo ........ go ahead and modify the code to suite your needs.

  Regards

  Srinivas Chilakapati

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  March 28, 2008 at 3:42 am

  #795036

  Refer below URLs as well.

  http://www.sqlservercentral.com/articles/Stored+Procedures/2977/

  http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx

  Thanks,

  MH-09-AM-8694