How to Avoid loop?

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  July 8, 2010 at 7:00 am

  #223065

  Is there any other way is there other than Loop

  i dont want to use loop to take MYNO

  Bulk of datas are there in table so i dont want to use loop to get that any other option

  declare @temp table (SNO INT IDENTITY(1,1),bulkdata VARCHAR(5000))

  declare @temp1 table (Myno VARCHAR(5000))

  declare @Input Nvarchar(1500)

  declare @output int

  declare @min-2 int, @max-2 int,@Transid VARCHAR(25)

  Insert into @temp

  Select'<MY>

  <Status>3</Status>

  <Myno>544781</Myno>

  <DateTime>06/07/2010 05:26:11 AM</DateTime>

  </MY>'

  union

  Select'<MY>

  <Status>3</Status>

  <Myno>425841</Myno>

  <DateTime>07/07/2010 05:26:11 AM</DateTime>

  </MY>'

  union

  Select'<MY>

  <Status>3</Status>

  <Myno>789841</Myno>

  <DateTime>08/07/2010 05:26:11 AM</DateTime>

  </MY>'

  select @min-2=min(sno),@max=max(sno) from @temp

  while @min-2<=@max

  begin

  Select @Input=bulkdata from @temp where sno=@min

  Select @Input='<NewDataSet>'+@Input+'</NewDataSet>'

  exec sp_xml_preparedocument @output output,@Input

  insert into @temp1

  Select * from openxml(@output,'NewDataSet/MY',2)

  with ( Myno Varchar(50) )

  set @min-2=@min+1

  end

  Select * from @temp1

  Thanks

  Parthi

  Thanks
  Parthi

• WayneS

  SSC Guru

  Points: 95460

  More actions

  July 8, 2010 at 8:30 am

  #1190282

  Hi Parthi,

  This is actually pretty easy to do... once you understand how to do it 😀

  First, you need to put the data into something with an XML data type. In your sample data below, I've added a "bulkdataxml" column of type XML, and after the data is loaded into the bulkdata column I update the bulkdataxml column with that data.

  declare @temp table (SNO INT IDENTITY(1,1),bulkdata VARCHAR(5000),bulkdataxml xml)

  declare @temp1 table (Myno VARCHAR(5000))

  Insert into @temp (bulkdata)

  Select'<MY>

  <Status>3</Status>

  <Myno>544781</Myno>

  <DateTime>06/07/2010 05:26:11 AM</DateTime>

  </MY>'

  union

  Select'<MY>

  <Status>3</Status>

  <Myno>425841</Myno>

  <DateTime>07/07/2010 05:26:11 AM</DateTime>

  </MY>'

  union

  Select'<MY>

  <Status>3</Status>

  <Myno>789841</Myno>

  <DateTime>08/07/2010 05:26:11 AM</DateTime>

  </MY>'

  update @temp

  set bulkdataxml = CONVERT(XML, bulkdata)

  Let's take a look at what we have now:

  select * from @temp

  Finally, shred the XML data, and extract the Myno element out, and insert it into the @temp1 table:

  INSERT INTO @temp1

  SELECT t2.data.value('Myno[1]','integer') as Myno

  FROM @temp t1

  CROSS APPLY t1.bulkdataxml.nodes('MY') AS t2(data)

  Select * from @temp1

  And we're done. No loop, just a simple insert statement.

  HTH,

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2