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

0md85ypi 于 12个月前发布在 SQL Server

关注(0)|答案(1)|浏览(122)

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

sql-server

来源：https://stackoverflow.com/questions/77637344/how-to-do-the-same-operation-as-openxml-but-with-the-nodes-method-in-sql-serve

1条答案

按热度按时间

new9mtju1#

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

赞(0）回复(0）举报 12个月前

我来回答

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

1条答案

相关问题

热门标签

最新问答