将XML解析并导入到SQL Server表中

Question

我已经编写了一个CLR程序集，可以将表格数据导出到XML文件中。现在我想将这些数据导入到另一个实例的临时表中。 XML文件的结构如下：

<row>
  <SystemInformationID>1</SystemInformationID>
  <Database_x0020_Version>10.00.80404.00</Database_x0020_Version>
  <VersionDate>2008-04-04T00:00:00</VersionDate>
  <ModifiedDate>2008-04-04T00:00:00</ModifiedDate>
</row>

我希望在目标位置解析XML并将其导入临时表中。 我已经在那里有了主表，所以我可以从那里获取表结构。

有没有办法？ 我使用OPENXML，但似乎无法正常工作。 我可以将XML文件读取到表中，该表将存储在具有XML数据类型的列中。 我的问题是解析该列中的数据。

这是一次尝试：

CREATE TABLE ##T (IntCol int, XmlCol xml)
GO

INSERT INTO ##T(XmlCol)
SELECT * FROM OPENROWSET(
   BULK 'c:\HISOutput.xml',
   SINGLE_CLOB) AS x
--works correctly up to this point

DECLARE @x xml
DECLARE @id int
SELECT @x=XmlCol FROM ##T

EXEC sp_xml_preparedocument @id OUTPUT, @x

SELECT    *
FROM       OPENXML (@id,'/row',2)
WITH 
dbo.awbuildversion

--I used dbo.awbuildversion table from AdventureWorks DB for testing
this doesn't show the first column no matter how I change the OPENXML instruction.

提前感谢你。

- Saam

---

如果您不至少缩进4个空格的XML，则SO软件会吃掉<和>字符。我已经为您修复了它。 - Jim Garrison

嗨，Jim，不知道那个。 - Saam

只需将您的代码行高亮显示，然后按编辑器工具栏上的“代码”按钮（101 010），即可获得正确的“代码格式化”。 - marc_s

Answer 1

我不太确定您想要什么，因为您对OMG Ponies的回答的评论与您的问题不同。临时表/表结构有什么问题？

无论如何，我不会在SQL Server 2050及以上版本（我认为您提到了CLR）上使用OPENXML或sp_xml_preparedocument，因为存在内存泄漏风险。

此外，如果您需要表结构，则可以使用INTO #tempTable。

DECLARE @foo xml

SET @foo = '<row>
  <SystemInformationID>1</SystemInformationID>
  <Database_x0020_Version>10.00.80404.00</Database_x0020_Version>
  <VersionDate>2008-04-04T00:00:00</VersionDate>
  <ModifiedDate>2008-04-04T00:00:00</ModifiedDate>
</row>'

SELECT
    bar.value('./SystemInformationID[1]','INT') AS 'SystemInformationID',
    bar.value('./Database_x0020_Version[1]','VARCHAR(14)') AS 'Database_x0020_Version',
    bar.value('./VersionDate[1]','DATETIME') AS 'VersionDate',
    bar.value('./ModifiedDate[1]','DATETIME') AS 'ModifiedDate'
INTO #tempTable    -- This?
FROM
    @foo.nodes('/row') AS foo(bar)     --use nodes not OPENXML

- gbn

---

谢谢你提供有关OPENXML和sp_xml_preparedocument的建议，我会牢记在心。我的问题在于select语句。我想将脚本交给我们的开发人员，让他在不同的数据库中运行不同的表格。如果我必须为每个表格在我的select命令中编写整个表格结构，那么这将是低效的。我想为每个表生成一个模板XML文件（如果可能的话），或者使用目标站点中现有的表结构（我们在每个站点都有相同的数据库和结构）。 - Saam

@Saba：我能建议的是在选择数据时使用XML模式来塑造数据。我自己没有使用过它们。然而，SQL是强定义和类型化的，同一个XML/查询在不同情况下返回不同列是没有意义的。除非你有针对每个目标表的不同XML。 - gbn

@gbn：我并不是指相同的查询会返回不同的结果。我的意思是，相同的查询会在不同结构的表中运行，而我不想每次为不同的表输入表结构。我希望能够针对每个表使用它自己的结构，或者生成一个特定于该表的 XML 模式。 - Saam

创建和重复使用模式的好例子可以在这个答案中看到。 - Amit Naidu