SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SCHEMA fn;
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;
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>');
1 rows affected

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


YourID
Field1
Field2
Field3


1
123
abc
XYZ

1
456
def
AEIOU


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


YourID
Field1
Field2
Field3


1
123
abc
XYZ

1
456
def
AEIOU


DROP TABLE dbo.YourTable;