SQL server : How to seperate XML nodes in different rows

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

CREATE TABLE #Tablexml
    ([Col1] int, [Col2] varchar(3), [Col3] xml)
;

INSERT INTO #Tablexml
    ([Col1], [Col2], [Col3])
VALUES
    (12, 'ABC', '<Interactions><interaction id=''2'' name=''A''/><interaction id=''3'' name=''B''/></Interactions>')
;


SELECT 
    A.Col1
    ,A.Col2
    ,Split.interaction.value('@id', 'INT') AS [ID]
    ,Split.interaction.value('@name', 'VARCHAR(10)') AS [Name]
 FROM  
 (
    SELECT Col1, Col2, Col3 FROM  #Tablexml
 ) AS A CROSS APPLY Col3.nodes ('/Interactions/interaction') AS Split(interaction);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results