SQL server how to change pipe separated column string to rows

I have 3 columns in a table, in which 2 columns have string separated by a '|' pipe. Both these columns values depend on each other.

For an example: I have data in the table like this :

ID  product quantity
1   A|B|C   1|2|3
2   X|Y|Z   7|8|9

I would like to change it to something like this :

ID  product quantity
1   A   1
1   B   2
1   C   3
2   X   7
2   Y   8
2   Z   9

As i am working with SSMS, i don't have any other choice except SQL. I try to use cross apply but i am not getting right result. For 1 row i receive 9 rows instead of getting 3. could anyone suggest me which method should i use?

Thank you in advance!! JACK

Solution

Test Data

CREATE TABLE #t (ID INT,  product VARCHAR(100) , quantity VARCHAR(100) )
INSERT INTO #t VALUES 
(1   ,'A|B|C' ,  '1|2|3'),
(2   ,'X|Y|Z' ,  '7|8|9');

Query

WITH Products AS (
        SELECT    ID
                , Product_Split.a.value('.', 'VARCHAR(100)') Products
                , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rn
        FROM (
              SELECT  ID
                     ,Cast ('<X>' 
                           + Replace(product, '|', '</X><X>') 
                           + '</X>' AS XML) AS Product_Data
                FROM #t
            ) AS t 
        CROSS APPLY Product_Data.nodes ('/X') AS Product_Split(a) 
),
 Quantities AS (
        SELECT    ID
                , Quantity_Split.a.value('.', 'VARCHAR(100)') Quantity
                , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rn
        FROM (
                SELECT  ID
                        ,Cast ('<X>' 
                            + Replace(quantity, '|', '</X><X>') 
                            + '</X>' AS XML) AS Quantity_Data
                FROM #t
            ) AS t 
        CROSS APPLY Quantity_Data.nodes ('/X') AS Quantity_Split(a)
 )
 SELECT   t.ID
        , P.Products
        , Q.Quantity

 FROM #t t
 LEFT JOIN Products     P   ON t.ID = p.ID
 LEFT JOIN Quantities   Q   ON Q.ID = t.ID 
                            AND Q.rn = p.rn

Result Set

╔════╦══════════╦══════════╗
║ ID ║ Products ║ Quantity ║
╠════╬══════════╬══════════╣
║  1 ║ A        ║        1 ║
║  1 ║ B        ║        2 ║
║  1 ║ C        ║        3 ║
║  2 ║ X        ║        7 ║
║  2 ║ Y        ║        8 ║
║  2 ║ Z        ║        9 ║
╚════╩══════════╩══════════╝