在SQL Query中使用while循环

using xp_dirtree, i fetch list of files in a folder. In the while loop, I am loading file by file to a Table.

使用xp_dirtree，我获取文件夹中的文件列表。在while循环中，我将文件按文件加载到表中。

My while loop syntax is incorrect , as the result loop is keep running and loading the same file.

我的while循环语法不正确，因为结果循环继续运行并加载相同的文件。

CREATE TABLE #t1(XmlCol XML)  

    create table #t (filename nvarchar(4000))
 insert into #t
          SELECT TOP 1 Subdirectory  FROM #directory
          WHERE [file] = 1 AND RIGHT(subdirectory, 4) = '.xml'

    WHILE EXISTS (SELECT TOP 1 1 FROM #directory)
    BEGIN


          INSERT INTO #T1(XmlCol)  
SELECT * FROM OPENROWSET(  
   BULK 'C:\Test\Test1.xml',  
   SINGLE_BLOB) AS x; 


    END

In the BULK 'C:\Test\Test1.xml' syntax, i need to pass file by file. Load file #1 and then next file. Without using Cursor, I am trying to achieve this by while loop.

在BULK'C：\ Test \ Test1.xml'语法中，我需要逐个文件传递。加载文件＃1，然后加载下一个文件。不使用Cursor，我试图通过while循环实现这一点。

1 个解决方案

#1

Why not just do this?

为什么不这样做呢？

insert into #t(filename)
  select Subdirectory  
  from #directory
  where [file] = 1 and subdirectory like '%.xml';

The select within the while does not appear to do anything useful.

在一段时间内的选择似乎没有做任何有用的事情。

#1