[SOLVED] How to do the same operation as OPENXML but with the nodes() method in SQL Server

How to do the same operation as OPENXML but with the nodes() method in SQL Server

In SQL Server, what would be the equivalent of this OPENXML command but using the nodes() method?

DECLARE @MyXML XML = '<data><Row><Name>Name A</Name><Type>T1</Type></Row><Row><Name>Name B</Name><Type>T2</Type></Row></Data>'

DECLARE @ID INT

EXEC SP_XML_PREPAREDOCUMENT @ID OUTPUT, @MyXML

SELECT
    [Value] AS 'Value',
    [Type] AS 'Type'
INTO #temp1
FROM OPENXML (@ID, '/Data/Row', 2)
WITH (
    [Value] NVARCHAR(100)
    [Type] NVARCHAR(100)
)

This is as far as I managed to get:

SELECT
    aaa.value('.', 'NVARCHAR(100)') AS 'Value'
INTO #temp1
FROM @MyXML.nodes('/Data/Row') AS T(aaa)

I could not figure out how to populate the [Type] field.

#temp1 should basically have 2 rows with the Name and Type columns. Each row would be:

Name A | T1
Name B | T2

Solution

Not sure why you thought . would get you results from the Name node, that just gets you the whole outer Row node. You need (Name/text())[1] instead. And select another column for the Type node.

SELECT
    x1.datarow.value('(Name/text())[1]', 'NVARCHAR(100)') AS Value,
    x1.datarow.value('(Type/text())[1]', 'NVARCHAR(100)') AS Type
FROM @MyXML.nodes('/Data/Row') AS x1(datarow);

db<>fiddle