Query from multiple XML columns in SQL Server

user_13732492 0
Viewed 108

Environment: SQL Server 2016; abstracted example of "real" data.

From a first query to a table containing XML data I have a SQL result set that has the following columns:

  1. ID (int)
  2. Names (XML)
  3. Times (XML)
  4. Values (XML)

Columns 2-4 contain multiple values in XML format, e.g.

Names:

  • Row 1: <name>TestR1</name><name>TestR2</name>...
  • Row 2: <name>TestS1</name><name>TestS2</name>...

Times:

  • Row 1: <time>0.1</time><time>0.2</time>...
  • Row 2: <time>-0.1</time><time>-0.2</time>...

Values:

  • Row 1: <value>1.1</value><value>1.2</value>...
  • Row 2: <value>-1.1</value><value>-1.2</value>...

The XML of all XML columns contain the exact same number of elements.

What I want now is to create a select that has the following output:

| ID | Name   | Time | Value |
+----+--------+------+-------+
| 1  | TestR1 |  0.1 |  1.1  |
| 1  | TestR1 |  0.2 |  1.2  |
| .. | ...... | .... | ..... |
| 2  | TestS1 | -0.1 | -1.1  |
| 2  | TestS2 | -0.2 | -1.2  |
| .. | ...... | .... | ..... |

For a single column CROSS APPLY works fine:

SELECT ID, N.value('.', 'nvarchar(50)') AS ExtractedName
FROM <source>
CROSS APPLY <source>.nodes('/name') AS T(N)

Applying multiple CROSS APPLY statements makes no sense here to me.

I would guess it would work if I would create selects for each column that then produce individual result sets and perform a select over all of the result sets but that's very likely not the best solution as I am duplicating selects for each additional column.

Any suggestions on how to design a query like that would be highly appreciated!

xml tsql sql-server
1 Answers

I'd suggest this approach:

First I create a declared table variable and fill it with your sample data to simulate your issue. This is called "MCVE", please try to provide this yourself in your next question.

DECLARE @tbl TABLE(ID INT, Names XML,Times XML,[Values] XML);
INSERT INTO @tbl VALUES
 (1,'<name>TestR1</name><name>TestR2</name>','<time>0.1</time><time>0.2</time>','<value>1.1</value><value>1.2</value>')
,(2,'<name>TestS1</name><name>TestS2</name>','<time>0.3</time><time>0.4</time>','<value>2.1</value><value>2.2</value>');

--The query

SELECT t.ID
      ,t.Names.value('(/name[sql:column("tally.Nmbr")])[1]','nvarchar(max)') AS [Name]
      ,t.Times.value('(/time[sql:column("tally.Nmbr")])[1]','decimal(10,4)') AS [Time]
      ,t.[Values].value('(/value[sql:column("tally.Nmbr")])[1]','decimal(10,4)') AS [Value]
FROM @tbl t
CROSS APPLY
(   
    SELECT TOP(t.Names.value('count(*)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) Nmbr FROM master..spt_values
) tally;

The idea in short:

  • We create a tally on the fly by using APPLY to create a list of numbers.
  • The TOP-clause will limit this list to the count of <name> elements in the given row.
  • In this case I take master..spt_values as a source for many rows. We do not need the content, just an appropriate list to create a tally. This said, if there is a physical numbers table in your database, this was even better.
  • Finally we can pick the content by the element's position using sql:column() to introduce the tally's value into the XQuery predicate.
user_50892040
Related