sql server - Open XML document and return for every row, based on row's path to xml file

Question

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.

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

Example values:
GUID | filePath
0001 | \serverfolderfile1.xml
0002 | \serverfolderfile2.xml
0003 | \serverfolderfile3.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, when every GUID has a different filepath?

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.

Answer 1

Your question is two folded:

• read the files with dynamically set file paths
• Find a query to read from the XMLs

Part 1: Read the files

Try this:

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));

--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;

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

SELECT * FROM StagingFileContent;

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)

Part 2: Read the XMLs

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);

The result:

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