SQL将每行项目导出到xml文件

Question

将SQL server数据库中的每个行项目导出到它自己的XML文件时出现问题。

我在网站上读到的所有问题都有各种各样的知识点，这些知识点都不能为我解决问题，我在知识上有一两个明显的差距，让我很失望。基本上，对于每个项目(一个名为BPAProcess的表中的大约350行，该表存在于6个数据库中)，每个数据库基本上都是一个不同的版本，它们彼此独立。因此，理想情况下，我可以在每个数据库上手动执行此查询6次，以便从该表中提取所有行)。我希望为每行提取XML并将其保存在本地，单元格的xml内容已经适合于它们只需要导出的xml结构。到目前为止，我已经尝试创建了一种循环机制来循环遍历我创建的临时表，然后对于每个行项目，将其提取到SQL命令中以保存详细信息。我已经到了BCP失败的地方，因为它看不到创建的临时表ive，在阅读了这个之后，我认为一旦我在其他设计中解决了这个问题，它就会失败，因为fiel输出上有8k的字符限制，但这些文件的长度约为300k。

    USE [BP6.8.1]
GO
 
-- Save XML records to a file:
DECLARE @fileName VARCHAR(175)
DECLARE @filePath VARCHAR(175)
DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

DECLARE @MaxRowsCount INT
DECLARE @Iter INT



select ROW_NUMBER() OVER (order by processid) row_num, name, processxml into #Process from BPAProcess
SET @MaxRowsCount = (SELECT MAX(row_num) FROM #Process)
SET @Iter = 1


WHILE @Iter <= @MaxRowsCount
BEGIN
 
SET @fileName = (select name from #Process where row_num = +@Iter)
SET @filePath = 'C:\Temp\sql queries\'+ @fileName +'.xml'
SET @sqlStr = 'select processxml from #Process where row_num ='+cast(@Iter as varchar(3))
 
SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @filePath + '" -w -T'
 
--EXEC xp_cmdshell @sqlCmd


SET @Iter = @Iter + 19090

-- +19090 just to execute first iteration only
END
Drop TABLE #Process

我觉得我需要的是某种方法来循环#进程表，并针对row_num中的每个项目，然后通过标准输出/类似方法导出它，或者创建一个循环机制来执行一个bpc命令，让sql指向我想要的xml单元格，但是我不知道如何让bpc看到我正在创建的表。

需要注意的是，这不是我的数据库，它是应用程序使用的数据库，不能更改任何内容。任务是获取每个单独的XML，并将其存储在一个另存为name.xml的文件驱动器上，其中名称在表中。我知道这不是使用SQL的正确方式(从到处阅读评论)，诚然，我不是一个SQL开发人员，我们手头也没有SQL开发人员，但我的任务是导出这段代码，在GUI上手动操作将需要几个星期的时间，因为这是一个漫长而费力的过程，而这会快得多。XML内容非常长，就像我说的，在某些情况下300k->500k。

感谢任何帮助，如果帮助是‘这不适合执行SQL’，那就好了，我可以用C#或其他语言来探索它，如果这确实不是应该这样做的话。

Answer 1

当你说某人粗鲁时，你应该期待一个实际的回答吗？具有重要经验的开发人员(您)应该能够找到可能的解决方案并评估其可用性。例如，搜索"sql server export one row to file“可以使用游标查找first entry。

坦率地说，您似乎在不同的语言方面有丰富的经验，所以我质疑您为什么选择基于TSQL的解决方案，而不是使用您擅长的语言。但这是一个非常不同的问题。不管什么语言/开发平台，RBAR仍然是RBAR。

转换为简单光标的代码如下所示。有些事情是你假设的，我再次建议你使用一门你精通的语言。

declare @sql varchar(500);
declare @name varchar(20);
declare c1 cursor  FAST_FORWARD for 
    select name from dbo.mytable order by name;
    
open c1;
fetch next from c1 into @name;
while @@fetch_status = 0
begin
   --print @name;

   set @sql = 'select processxml from dbo.mytable where name = ''' + @name + ''''; 
   set @sql = 'bcp "' + @sql + '" queryout "' + 'C:\Temp\sql queries\'+ @name +'.xml " -w +T'
   print @sql; 
   
   fetch next from c1 into @name;
end;

close c1;
deallocate c1;

您可能需要调整此设置，以使用特定的登录名连接到正确的实例和数据库。documentation提供了一些光标示例--总是一个很好的起点。也许您应该尝试的第一件事是在命令提示符下使用BCP将单个特定行中的列导出到特定文件，以便首先验证您的假设和预期。

只需少量的工作，您就可以添加一些额外的逻辑来为给定实例中的多个数据库运行它。这样做需要处理生成的文件集中的名称冲突。

最后，按原样编写代码，但不使用BCP命令依赖临时表。请注意，由于小提琴中的简化表，所以进行了微小的调整。

--select ROW_NUMBER() OVER (order by processid) row_num, name, processxml 
select ROW_NUMBER() OVER (order by name) row_num, name 
   into #Process 
   from dbo.mytable
SET @MaxRowsCount = (SELECT MAX(row_num) FROM #Process)
SET @Iter = 1

WHILE @Iter <= @MaxRowsCount
BEGIN
 
   SET @fileName = (select name from #Process where row_num = +@Iter)
   SET @filePath = 'C:\Temp\sql queries\'+ @fileName +'.xml'
   SET @sqlStr = 'select processxml from dbo.mytable where row_num ='+cast(@Iter as varchar(3))
 
   SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @filePath + '" -w -T'
   print @sqlCmd;
   --EXEC xp_cmdshell @sqlCmd

   SET @Iter = @Iter + 1
END
Drop TABLE #Process

fiddle来演示这两者。

Answer 2

This is really not a job for T-SQL，它不是一种通用的脚本语言，它只用于查询。

相反，可以使用Powershell将XML提取为单独的行，然后将它们输出到文件中

Invoke-Sqlcmd
  -ServerInstance "YourServer"
  -Database "YourDB" -Username "user" -Password "pass"
  -Query "select name, processxml from dbo.mytable;"
| % {
    Out-File -FilePath ("Downloads\temp\" + $_.name + ".xml") -InputObject $_.processxml
}

您也可以使用任何其他客户端应用程序来做同样的事情，这比在T-SQL中容易得多。