在SQL Server中将XML值转换为行，如oracle XAMLTABLE

DECLARE @xmlData XML = '
<Root>
    <Item>
        <ID>1</ID>
        <Name>Item1</Name>
    </Item>
    <Item>
        <ID>2</ID>
        <Name>Item2</Name>
    </Item>
</Root>';

SELECT 
    T.c.value('(ID/text())[1]', 'INT') AS ID,
    T.c.value('(Name/text())[1]', 'NVARCHAR(50)') AS Name
FROM 
    @xmlData.nodes('/Root/Item') AS T(c);

DECLARE @idoc INT;
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData;

SELECT 
    ID,
    Name
FROM 
    OPENXML(@idoc, '/Root/Item')
    WITH (
        ID INT 'ID',
        Name NVARCHAR(50) 'Name'
    );

EXEC sp_xml_removedocument @idoc;

SELECT 
    T.c.value('(ID/text())[1]', 'INT') AS ID,
    T.c.value('(Name/text())[1]', 'NVARCHAR(50)') AS Name
FROM 
    (SELECT @xmlData AS XMLData) AS T
CROSS APPLY 
    XMLData.nodes('/Root/Item') AS T(c);