Извлечение значений из нескольких столбцов XML, каждый из которых имеет несколько узлов

Досадно (насколько мне известно), вы не можете вернуть значение позиции из узла, вы можете только фильтровать его (как показано в этом ответе от Роджера Вольфа).

Вместо этого вы могли бы создать счетчик количества узлов в первом столбце XML, чтобы получить строку для каждого узла p для каждого идентификатора. Затем вы можете фильтровать position() при вызове метода nodes и возвращать по 1 строке на позицию.

Поскольку вы находитесь в 2019 году, у вас нет доступа к GENERATE_SERIES, поэтому для подсчета я использую UDF:

--Create the UDF
CREATE FUNCTION [fn].[Tally] (@LastNumber bigint, @Zero bit) 
RETURNS table
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @Zero = 0
          AND @LastNumber IS NOT NULL
        UNION ALL
        SELECT TOP (ISNULL(@LastNumber,0))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
    SELECT I
    FROM Tally T;
GO
--Create sample data
CREATE TABLE dbo.YourTable (YourID int IDENTITY,
                            XML1 xml,
                            XML2 xml,
                            XML3 xml);
INSERT INTO dbo.YourTable
VALUES('<div class = "Class1">
  <p>123</p>
  <p>456</p>
</div>','<div class = "Class2">
<p>abc</p>
<p>def</p>
</div>','<div class = "Class3">
<p>XYZ</p>
<p>AEIOU</p>
</div>');
GO
--The actual solution
WITH NodeCounts AS(
    SELECT YT.YourID,
           COUNT(*) AS Nodes
    FROM dbo.YourTable YT
         CROSS APPLY YT.XML1.nodes('/div/p') div(p)
    GROUP BY YT.YourID)
SELECT YT.YourID,
       X1.p.value('(./text())[1]','varchar(30)') AS Field1,
       X2.p.value('(./text())[1]','varchar(30)') AS Field2,
       X3.p.value('(./text())[1]','varchar(30)') AS Field3
FROM dbo.YourTable YT
     JOIN NodeCounts NC ON YT.YourID = NC.YourID
     CROSS APPLY fn.Tally(NC.Nodes,1) T
     CROSS APPLY YT.XML1.nodes('/div/p[position() = sql:column("T.I")]') X1(p)
     CROSS APPLY YT.XML2.nodes('/div/p[position() = sql:column("T.I")]') X2(p)
     CROSS APPLY YT.XML3.nodes('/div/p[position() = sql:column("T.I")]') X3(p);

Альтернативно, вы можете добиться этого без Tally и использовать ROW_NUMBER в CTE. Это может быть более производительно (я предлагаю участвовать в гонках на лошадях):

WITH NodeCounts AS(
    SELECT YT.YourID,
           YT.XML1,
           YT.XML2,
           YT.XML3,
           ROW_NUMBER() OVER (PARTITION BY YT.YourID ORDER BY (SELECT NULL)) AS I
    FROM dbo.YourTable YT
         CROSS APPLY YT.XML1.nodes('/div/p') div(p))
SELECT NC.YourID,
       X1.p.value('(./text())[1]','varchar(30)') AS Field1,
       X2.p.value('(./text())[1]','varchar(30)') AS Field2,
       X3.p.value('(./text())[1]','varchar(30)') AS Field3
FROM NodeCounts NC
     CROSS APPLY NC.XML1.nodes('/div/p[position() = sql:column("NC.I")]') X1(p)
     CROSS APPLY NC.XML2.nodes('/div/p[position() = sql:column("NC.I")]') X2(p)
     CROSS APPLY NC.XML3.nodes('/div/p[position() = sql:column("NC.I")]') X3(p);

db<>рабочий пример

Пожалуйста, попробуйте следующее решение.

Следующий XQuery p.value('for $i in . return count(../p[. << $i]) + 1', 'INT') AS Seq позволяет нам легко вернуть позицию элемента XML. Остальное — это просто объединения на основе значений seq(ence).

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,Col1 XML,Col2 XML,Col3 XML);
INSERT INTO @tbl(Col1,Col2,Col3) 
VALUES 
('<div class = "Class1">
  <p>123</p>
  <p>456</p>
</div>)'
,'<div class = "Class2">
<p>abc</p>
<p>def</p>
</div>'
 ,'<div class = "Class3">
<p>XYZ</p>
<p>AEIOU</p>
</div>');
-- DDL and sample data population, start

WITH rs1 AS
(
    SELECT t.id
        , p.value('for $i in . return count(../p[. << $i]) + 1', 'INT') AS Seq
        , p.value('text()[1]', 'VARCHAR(20)') AS col1
    FROM @tbl AS t
    CROSS APPLY Col1.nodes('/div/p') div(p)
), rs2 AS
(
    SELECT t.id
        , p.value('for $i in . return count(../p[. << $i]) + 1', 'INT') AS Seq
        , p.value('text()[1]', 'VARCHAR(20)') AS col2
    FROM @tbl AS t
    CROSS APPLY Col2.nodes('/div/p') div(p)
), rs3 AS
(
    SELECT t.id
        , p.value('for $i in . return count(../p[. << $i]) + 1', 'INT') AS Seq
        , p.value('text()[1]', 'VARCHAR(20)') AS col3
    FROM @tbl AS t
    CROSS APPLY Col3.nodes('/div/p') div(p)
)
SELECT rs1.ID, col1, col2, col3 
FROM rs1 
    INNER JOIN rs2 ON rs2.ID = rs1.ID AND rs2.Seq = rs1.Seq
    INNER JOIN rs3 ON rs3.ID = rs1.ID AND rs3.Seq = rs1.Seq;

Выход

В подтверждение трюка Ицхака с XML, вот сокращенная версия:

SELECT  *
FROM    (
    SELECT  p.value('for $i in . return count(../p[. << $i]) + 1', 'INT') AS Seq
    ,   p.value('text()[1]', 'VARCHAR(20)') AS col
    ,   x.id
    FROM    (
        VALUES 
            ('<div class = "Class1">
              <p>123</p>
              <p>456</p>
            </div>)'
            ,'<div class = "Class2">
            <p>abc</p>
            <p>def</p>
            </div>'
             ,'<div class = "Class3">
            <p>XYZ</p>
            <p>AEIOU</p>
            </div>')
        ) data (Col1, Col2, Col3)
    CROSS APPLY (
        VALUES  (CAST(Col1 AS XML), 1)
        ,   (CAST(Col2 AS XML), 2)
        ,   (CAST(Col3 AS XML), 3)
        ) x (col, id)
    CROSS APPLY x.col.nodes('/div/p') div(p)
    ) x
PIVOT(MAX(col) FOR id IN ([1],[2],[3])) pv

1. Сначала мы расширяем столбцы на 3 строки с помощью XML, ID (подзапрос x), а затем выполняем узлы CROSS APPLY и извлекаем Position и

   ценить

   .
2. Затем, с помощью PIVOTing по идентификатору, мы превращаем строки обратно в столбцы, сгруппированные по последовательности.

Выход: