I am trying to convert an XML variable to SQL table using openxml but I am not getting the result.
Declare @List xml = '<List><CNames>One</CNames><CNames>two</CNames></List>'
Ideally I want like
Cnames -- columname
One
two
I am beginner tried google but no luck - TIA
First of all: FROM OPENXML is outdated and should not be used any more. Rather use the native methods supported by the XML data type.
Your question is not very clear, but my magic crystall ball told me, that you might be looking for something along this:
Declare @List xml = '<List><CNames>One</CNames><CNames>two</CNames></List>';
SELECT A.x.value('text()[1]','nvarchar(max)') AS CName
--INTO dbo.SomeTable
FROM @List.nodes('/List/CNames') A(x);
This will return the XML's content as a tabular result.
Just take away the -- (-> uncomment) before the INTO and you will find a new table SomeTable among your database's tables with this content.
If this does not help you, please try to use the edit option of your answer and add some more details.
