split_string_xml.sql

SELECT    p.SubscriberKey
        , c.OrderIDs
FROM [AllOrders] p
CROSS APPLY (
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[1]','nvarchar(max)') as OrderIDs1
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[2]','nvarchar(max)') as OrderIDs2
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[3]','nvarchar(max)') as OrderIDs3
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[4]','nvarchar(max)') as OrderIDs4
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[5]','nvarchar(max)') as OrderIDs5
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[6]','nvarchar(max)') as OrderIDs6
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[7]','nvarchar(max)') as OrderIDs7
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[8]','nvarchar(max)') as OrderIDs8
    UNION ALL
    Select  CAST('<x>' + REPLACE(p.OrderIDs,',','</x><x>') + '</x>' AS XML).value('/x[9]','nvarchar(max)') as OrderIDs9
) c (OrderIDs)
WHERE ISNULL(c.OrderIDs,'') <> ''