XML Usage

• mikhailk2

  Newbie

  Points: 1

  More actions

  September 9, 2008 at 10:57 am

  #193326

  so i have a xml that is passed into a stored procedure.

  ex

  I want to make a table out of that data as follows

  ID Supervisor Center

  ---- ----------- ---------

  1 11 NULL

  2 11 NULL

  3 11 NULL

  5 12 NULL

  6 12 NULL

  7 12 Dallas

  8 NULL Dallas

  9 NULL Dallas

  can someone show me how to do this?

  thanks

  Mike

• DB_Andrew

  Ten Centuries

  Points: 1089

  More actions

  September 10, 2008 at 12:01 pm

  #869869

  I cannot see your XML example but this should get you started.

  Note: Replace the ( and ) with the correct XML tag indicators.

  DECLARE @XML XML

  SET @XML =

  '(root)

  (row)

  (ID)1(/ID)

  (Supervisor)1(/Supervisor)

  (Center)Dallas(/Center)

  (/row)

  (row)

  (ID)2(/ID)

  (Supervisor)2(/Supervisor)

  (Center)Dallas(/Center)

  (/row)

  (row)

  (ID)3(/ID)

  (Supervisor)3(/Supervisor)

  (Center)Dallas(/Center)

  (/row)

  (/root)'

  SELECT

  x.value('ID[1]','int') AS [ID]

  ,x.value('Supervisor[1]','int') AS [Supervisor]

  ,x.value('Center[1]','VARCHAR(30)') AS [Center]

  FROM

  @XML.nodes('/root/row') y(x);