USE tempgb
go
CREATE TABLE PipeData (id          int           NOT NULL, 
                       ShipName    nvarchar(MAX) NOT NULL, 
                       ShipAddress nvarchar(MAX) NOT NULL,
                       CONSTRAINT pk_PipeData PRIMARY KEY (id)
)
go
INSERT PipeData(id, ShipName, ShipAddress)
   SELECT CustomerID, string_agg(convert(nvarchar(MAX), ShipName), '|') WITHIN GROUP (ORDER BY OrderID), 
                      string_agg(convert(nvarchar(MAX), ShipAddress), '|') WITHIN GROUP (ORDER BY OrderID)
   FROM   BigDB..BigOrders
   GROUP  BY CustomerID
go
------------------------------------------------- Yithak's XML. --------------------------------------
DROP TABLE IF EXISTS #temp1
DECLARE @d datetime2 = sysdatetime()
 DECLARE @separator CHAR(1) = '|';
    
 ;WITH rs AS
 (
  SELECT id
  , TRY_CAST('<root><r>' + 
  REPLACE(ShipName + @separator + ShipAddress, @separator, '</r><r>') + 
  '</r></root>' AS XML).query('<root>
  {
     let $counter := count(/root/r) div 2
     for $x in /root/r[position() le $counter]
     let $pos := count(/root/r[. << $x[1]]) + 1
     return <r name="{$x/text()}" city="{/root/r[$pos + $counter]/text()}"></r>
  }
  </root>') AS xmldata
  FROM PipeData
 )
 SELECT rs.id
  , n.value('@name','VARCHAR(30)') AS [name]
  , n.value('@city','VARCHAR(30)') AS [city]
INTO #temp1
 FROM rs 
  CROSS APPLY xmldata.nodes('/root/r') AS t(n)
 ORDER BY rs.id;
go
 DROP TABLE IF EXISTS #temp2
----------------------------------------------------------- Ronen's JSON ------------------------------------------------
DECLARE @d datetime2 = sysdatetime()

 ;With MyCTE0 as (
  select id, ShipName,ShipAddress
  ,s = '["' + Replace(ShipName, '|', '","') + '"]'
  ,s1 = '["' + Replace(ShipAddress, '|', '","') + '"]'
  from PipeData
 ),
 MyCTE1 as (
  select id, s, s1
  , k1 = t.[key], v1 = t.[value]
  from MyCTE0
  CROSS APPLY OPENJSON (s, N'$') t
 )
 SELECT id, v1 , v2
 INTO #temp2
 FROM MyCTE1
 CROSS APPLY (SELECT t1.[key] k2 , t1.[value] v2 FROM OPENJSON (s1, N'$') t1 where t1.[key] = MyCTE1.k1) t
 SELECT datediff(ms, @d, sysdatetime())
go
--------------------------------------------------------------------- My Function ------------------------------
CREATE FUNCTION iter_charlist_to_tbl
                 (@list      nvarchar(MAX),
                  @delimiter nchar(1) = N',')
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          str     varchar(4000)      NOT NULL,
                          nstr    nvarchar(2000)     NOT NULL) AS

BEGIN
   DECLARE @endpos   int,
           @startpos int,
           @textpos  int,
           @chunklen smallint,
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000),
           @tmpval   nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SET @chunklen = 4000 - datalength(@leftover) / 2
      SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
      SET @textpos = @textpos + @chunklen

      SET @startpos = 0
      SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   INSERT @tbl(str, nstr)
      VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
END
go
DROP TABLE IF EXISTS #temp3
DECLARE @d datetime2 = sysdatetime()


 SELECT t.id, s.str + ' ' + s1.str AS ShipStr
 INTO   #temp3
 FROM   PipeData t
 CROSS  APPLY iter_charlist_to_tbl(t.ShipName, '|') AS s
 CROSS  APPLY iter_charlist_to_tbl(t.ShipAddress, '|') AS s1
 WHERE  s.listpos = s1.listpos

 SELECT datediff(ms, @d, sysdatetime())

go
-------------------------------------------------------- My temp tables ------------------------------------------
DROP TABLE IF EXISTS #t1
DROP TABLE IF EXISTS #t2
DROP TABLE IF EXISTS #temp4
go
DECLARE @d datetime2(3) = sysdatetime()

CREATE TABLE #t1 (id  int NOT NULL,
                  pos int NOT NULL,
                  ShipName nvarchar(60) NOT NULL,
                  PRIMARY KEY (id, pos))
CREATE TABLE #t2 (id  int NOT NULL,
                  pos int NOT NULL,
                  ShipAddress nvarchar(60) NOT NULL,
                  PRIMARY KEY (id, pos))

INSERT #t1(id, pos, ShipName)
  SELECT t.id, s.listpos, s.nstr
  FROM   PipeData t
  CROSS  APPLY iter_charlist_to_tbl(t.ShipName, '|') AS s

INSERT #t2(id, pos, ShipAddress)
  SELECT t.id, s.listpos, s.nstr
  FROM   PipeData t
  CROSS  APPLY iter_charlist_to_tbl(t.ShipAddress, '|') AS s

SELECT t1.id, t1.ShipName + ' ' + t2.ShipAddress as str
INTO   #temp4
FROM   #t1 t1
JOIN   #t2 t2 ON t1.id = t2.id
             AND t1.pos = t2.pos

 SELECT datediff(ms, @d, sysdatetime())