Query from multiple XML columns in SQL Server

Question

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:

ID (int)
Names (XML)
Times (XML)
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!

Gottfried Lesigang · Accepted Answer · 2020-06-12 08:10:59Z

2

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.

answered Jun 12, 2020 at 8:10

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zhorov Over a year ago

Using a tally table to get the correct position for each element should be the first option in this case. +1.

Blobfish Over a year ago

Thank you so much for you answer. Just implemented your appoach in minutes and it's working like a charm!

Yitzhak Khabinsky Over a year ago

Good answer, +1 from my side!

Collectives™ on Stack Overflow

Query from multiple XML columns in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related