打开 XML 文档并根据行的 xml 文件路径返回每一行

Open XML document and return for every row, based on row#39;s path to xml file(打开 XML 文档并根据行的 xml 文件路径返回每一行)

问题描述

我对在 TSQL 中使用 XML 还很陌生，最近遇到了一个我想不通的问题，需要您的帮助.

I'm pretty new to using XML with TSQL, and recently ran into an issue that I can't think around and need your help with.

场景:我构建了一个查询，它返回相当多的列，其中一个包含 .xml 文件的 UNC 路径.为简单起见，假设有 2 列:GUID, filePath

Scenario: I have a query built that returns quite a few columns, one of which contains a UNC path to an .xml file. For simplicity's sake, lets just say there's 2 columns: GUID, filePath

示例值:
图形用户界面 |文件路径
0001 |\服务器文件夹file1.xml
0002 |\服务器文件夹file2.xml
0003 |\服务器文件夹file3.xml

Example values:
GUID | filePath
0001 | \serverfolderfile1.xml
0002 | \serverfolderfile2.xml
0003 | \serverfolderfile3.xml

目标:我想要返回的每个 xml 文件中的三个字段值(如下所示)，但除了我想要的三个之外，还有其他字段.

Goal: There are three field values in each xml file that I want returned (shown below) but there are additional fields over than just the three I want.

xml:

<form>
  <field>
    <name>TextboxAllocation3</name>
    <value>0</value>
  </field>
  <field>
    <name>TextboxAllocation1</name>
    <value>0</value>
  </field>
  <field>
    <name>TextboxAllocation2</name>
    <value>0</value>
  </field>
...
</form>

问题:我怎样才能制作一个返回的查询:

Question: How could I craft a query that would return:

GUID、TextboxAllocation1、TextboxAllocation2、TextboxAllocation3，当每个 GUID 有不同的文件路径时?

GUID, TextboxAllocation1, TextboxAllocation2, TextboxAllocation3, when every GUID has a different filepath?

我尝试过的:
• 使用openrowset，但指定目标不能是变量(或者在这种情况下，它不能是查询中的filePath)，它必须是文本，这导致我走上了快速变成动态SQL的路径意大利面的融合，我意识到我现在无法思考.

What I've tried:
• Using openrowset, but specifying the target can't be a variable (or in this case, it can't be the filePath from the query), it must be text, which lead me down the path of dynamic SQL which quickly turned into an amalgamation of spaghetti that I'm realizing I can't think through right now.

推荐答案

你的问题有两个方面:

• 使用动态设置的文件路径读取文件
• 查找要从 XML 中读取的查询

试试这个:

DECLARE @mockup TABLE([GUID] VARCHAR(100),filePath VARCHAR(100));
INSERT INTO @mockup VALUES
 ('0001','\YourPathFile1.xml')
,('0002','\YourPathFile2.xml')
,('0003','\YourPathFile3.xml');

--使用物理创建的表作为临时表

--Use a physically created table as staging table

CREATE TABLE StagingFileContent([GUID] VARCHAR(100),FileContent VARBINARY(MAX));

--游标循环将读取文件名并使用动态 SQL 调用 OPENROWSET

--A cursor-loop will read the file names and call OPENROWSET with dynamic SQL

DECLARE @g VARCHAR(100),@fp VARCHAR(100);
DECLARE @cmd VARCHAR(MAX);

DECLARE cur CURSOR FOR SELECT [GUID],filePath FROM @mockup;
OPEN cur;
FETCH NEXT FROM cur INTO @g,@fp;
WHILE @@FETCH_STATUS=0
BEGIN
    SET @cmd='INSERT INTO StagingFileContent([GUID],FileContent) ' +
             'SELECT ''' + @g + ''',* FROM OPENROWSET(BULK ''' +  @fp + ''', SINGLE_BLOB) AS Contents;'
    EXEC(@cmd);
FETCH NEXT FROM cur INTO @g,@fp;
END 
CLOSE cur;
DEALLOCATE cur;

--现在暂存表将 GUID 和内容保存为 VARBINARY(MAX)

--Now the staging table holds the GUID and the content as VARBINARY(MAX)

SELECT * FROM StagingFileContent;

提示:

这可能取决于文件的编码.您可以尝试 SINGLE_CLOB 并使用 VARCHAR(MAX) 或 NVARCHAR(MAX) 而不是 VARBINARY(MAX)

Hint:

This might be depending on the encoding of your files. You can try SINGLE_CLOB and using VARCHAR(MAX) or NVARCHAR(MAX) instead of VARBINARY(MAX)

试试这个，该表正在模拟您的临时表:

Try this, the table is simulating your staging table:

DECLARE @xmls TABLE([GUID] VARCHAR(100),FileContent VARBINARY(MAX));
INSERT INTO @xmls VALUES
('0001',CAST(N'<form>
              <field>
                <name>TextboxAllocation3</name>
                <value>0</value>
              </field>
              <field>
                <name>TextboxAllocation1</name>
                <value>0</value>
              </field>
              <field>
                <name>TextboxAllocation2</name>
                <value>0</value>
              </field>
            </form>' AS VARBINARY(MAX)))
,('0002',CAST(N'<form>
              <field>
                <name>SomeMore</name>
                <value>1</value>
              </field>
              <field>
                <name>EvenMore</name>
                <value>2</value>
              </field>
            </form>' AS VARBINARY(MAX)));
WITH Casted AS
(
    SELECT [GUID],CAST(CAST(FileContent AS NVARCHAR(MAX)) AS XML) AS YourXML
    FROM @xmls
)
SELECT [GUID]
      ,f.value(N'(name/text())[1]','nvarchar(max)') AS FieldName
      ,f.value(N'(value/text())[1]','nvarchar(max)') AS FieldValue
FROM Casted
CROSS APPLY YourXML.nodes(N'/form/field') AS A(f);

结果:

GUID    FieldName          FieldValue
0001    TextboxAllocation3  0
0001    TextboxAllocation1  0
0001    TextboxAllocation2  0
0002    SomeMore            1
0002    EvenMore            2

这篇关于打开 XML 文档并根据行的 xml 文件路径返回每一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！