loop with OPENXML

• hren

  Ten Centuries

  Points: 1220

  More actions

  June 28, 2006 at 7:50 am

  #168990

  So I have my XML doc with whole bunch of data. For this specific INSERT I just need few records. So I try:

  DECLARE @number int

  SET @number = 1

  WHILE @number < 8
  BEGIN
  INSERT INTO SomeTable
  (
  UserID,
  SomeColumn,
  SomeOtherColumn,
  SomeThirdColumn
  )
  SELECT
  UserID,
  SomeColumn,
  SomeOtherColumn,
  SomeThirdColumn
  FROM OPENXML(@intDoc,'/DATA/ITEM')
  WITH (
  UserID int @userid,

  SomeColumn varchar(100) 'Paper' + @number + 'Sheet' ,

  SomeOtherColumn varchar(50) 'Block' + @number + 'Num' ,

  SomeThirdColumn varchar(50) 'License' + @number + 'Valid'

  )

  SET @number = @number + 1

  END

  The reason to this as there is N amount of columns in my XML doc that go like this:

  Paper1Sheet

  Block1Num

  License1Valid

  Paper2Sheet

  Block2Num

  License2Valid

  Paper3Sheet

  Block3Num

  License3Valid

  ...

  PaperNNSheet

  BlocNNNum

  LicenseNNValid

  So I'm trying to insert them all into a table regardless of how many there are.

  PS: Btw, it doesn't like @userid inserted like that either.

  Hope someone can help me resolve this.

• Mikhail Berlyant

  SSC Rookie

  Points: 37

  More actions

  June 29, 2006 at 1:38 pm

  #646830

  just run this example in query analyzer. hope this will help you to proceed with your specifics

  -- Start Code

  declare @doc nvarchar(4000)

  set @doc =

  '

  <data>

  <paper1sheet>value for paper1sheet</paper1sheet>, <block1num>value for block1num</block1num>, <license1valid>value for license1valid</license1valid>

  <paper2sheet>value for paper2sheet</paper2sheet>, <block2num>value for block2num</block2num>, <license2valid>value for license2valid</license2valid>

  <paper3sheet>value for paper3sheet</paper3sheet>, <block3num>value for block3num</block3num>, <license3valid>value for license3valid</license3valid>

  <paper4sheet a="11">value for paper4sheet</paper4sheet>, <block4num>value for block4num</block4num>, <license4valid>value for license4valid</license4valid>

  <paper5sheet a="12">value for paper5sheet</paper5sheet>, <block5num>value for block5num</block5num>, <license5valid>value for license5valid</license5valid>

  <paper6sheet>value for paper6sheet</paper6sheet>, <block6num>value for block6num</block6num>, <license6valid>value for license6valid</license6valid>

  <paper7sheet>value for paper7sheet</paper7sheet>, <block7num>value for block7num</block7num>, <license7valid>value for license7valid</license7valid>

  </data>

  '

  declare @xml table(columnname varchar(100), columnvalue varchar(1000))

  declare @dochandle integer

  exec sp_xml_preparedocument @dochandle output, @doc

   insert into @xml

   select  

    columnname,

    columnvalue

   from openxml(@dochandle,'/data/*')

    with (

    columnname varchar(8000) '@mp:localname' ,

    columnvalue varchar(8000) './text()'

   &nbsp as t1

  exec sp_xml_removedocument @dochandle  

  select  

   replace(replace(t1.columnname,'paper',''),'sheet','') as id,

   t1.columnvalue as column1,

   t2.columnvalue as column2,

   t3.columnvalue as column3

  from

   @xml as t1

  join

   @xml as t2

  on

   replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t2.columnname,'block',''),'num','')

  and

   isnumeric(replace(replace(t2.columnname,'block',''),'num','')) = 1

  join

   @xml as t3

  on

   replace(replace(t1.columnname,'paper',''),'sheet','') = replace(replace(t3.columnname,'license',''),'valid','')

  and

   isnumeric(replace(replace(t3.columnname,'license',''),'valid','')) = 1

  where

   isnumeric(replace(replace(t1.columnname,'paper',''),'sheet','')) = 1

  -- End Code