Get XML node from SQL Server query

Question

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

Pரதீப் · Accepted Answer · 2017-01-21 11:26:18Z

1

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)

answered Jan 21, 2017 at 11:26

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2726975 Over a year ago

I had one more clarification, when do you use '/' and when don't you use. Thanks so much

Pரதீப் Over a year ago

@user2726975 - Am not a expert in xml but I have seen in value method start with / when XQuery(first parameter) starts with root. Something like value('(/root/innernode/info)[1]', 'VARCHAR(100)' )

Gottfried Lesigang Over a year ago

@user2726975 But in simple words: 1-slash = move one level down, 2-slashes=*find any node below*, no slash = relative to the context node. You start to navigate at the root, than you move down (and up/to the side) the path. The current position is the context node. Any path starts from there. In the very beginning the root node is the context node. Here you'll find a comprehensive overview

Pரதீப் Over a year ago

@Shnugo - Thanks.. TBH it was helpful to me in understanding

Collectives™ on Stack Overflow

Get XML node from SQL Server query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related