CODEWITHSUNDEEP
sql-serverxml
user2726975
user2726975

Reputation: 1353

Get XML node from SQL Server query

I am trying to get to a node in XML using SQL Server.

CREATE TABLE temp.testXML(ID INT, xmlinput XML NULL);

INSERT INTO temp.testXML 
VALUES (1, '<root><node1><lang>English</lang></node1><innernode><info>20170117T022113</info></innernode></root>')

I want to print "info" node i.e "20170117T022113"

SELECT 
    t.id,
    x.XmlCol.value('(/innernode/info)[1]', 'VARCHAR(100)') AS dt
FROM 
    [temp].[testxml] as t
CROSS APPLY 
    t.rawxml.nodes('/root') as x(XmlCol)

I am getting a null.

How can I get to the node value??

Thanks MR

Upvotes: 0

Views: 2281

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

There are two mistakes in your query

  • Use xmlinput column name in nodes function instead rawxml
  • Remove the first / in value function

Query 

SELECT t.id,
       x.XmlCol.value('(innernode/info)[1]', 'VARCHAR(100)') AS dt
FROM   [testxml] AS t
       CROSS APPLY t.xmlinput.nodes('/root') AS x(XmlCol)

Upvotes: 1

Related Questions