sql - Need to fetch both Columns in XML Nodes?

Question

Is it possible to get the 'A' and 'B' Columns in the Same(Single) SELECT Statement instead of using the 2 SELECT Statements:

Tried query:

DECLARE @Invoice_Types  VARCHAR(MAX)='T-E,T-I,3-E,D-E,D-I'
,@Invoice_TypesXML XML

SET @Invoice_TypesXML=CAST(('<I>'+REPLACE(REPLACE(@Invoice_Types,',','</R><I>'),'-' ,'</I><R>') +'</R>') 
AS XML);

SELECT I.value('.', 'VARCHAR(MAX)') A                        
FROM @Invoice_TypesXML.nodes('I') AS FN(I)

SELECT R.value('.', 'VARCHAR(MAX)') B                       
FROM @Invoice_TypesXML.nodes('R') AS FN(R)

Expected Result: in Single Select Statement

 A   B
-----------------
T   E
T   I
3   E
D   E
D   I  

Answer 1

For your requirement, you need to revise your XML structure like this:

    <Item>
          <I>T</I><R>E</R>
   </Item>
    <Item>
          <I>T</I><R>I</R>
   </Item>
    <Item>
          <I>3</I><R>E</R>
   </Item>
    <Item>
          <I>D</I><R>E</R>
   </Item>
    <Item>
          <I>D</I><R>I</R>
   </Item>

And then you can select your final result in one select statement.

Try this:

DECLARE @Invoice_Types  VARCHAR(MAX)='T-E,T-I,3-E,D-E,D-I'
,@Invoice_TypesXML XML

SET @Invoice_TypesXML=CAST(('<Item><I>'+REPLACE(REPLACE(@Invoice_Types,',','</R></Item><Item><I>'),'-' ,'</I><R>') +'</R></Item>') 
AS XML);


SELECT 
    x.Rec.query('./I').value('.', 'nvarchar(2000)') AS 'A',
    x.Rec.query('./R').value('.', 'nvarchar(2000)') AS 'B'
FROM @Invoice_TypesXML.nodes('/Item') as x(Rec)

Final result:

A   B
_____
T   E
T   I
3   E
D   E
D   I

See this Fiddle