Select xml nodes that have dot in the name using XPath

Question

I am trying to select text nodes of Lookup.Result element of the following xml document using XPath in Sql Server.

<Commands>
<Command id="1">
<Lookup.Result>Result.OK</Lookup.Result>
</Command>
</Commands>

I try the following query:

declare @xml xml
set @xml = '<Commands>
    <Command id="1">
    <Lookup.Result>Result.OK</Lookup.Result>
    </Command>
    </Commands>
'
select t.c.value('./Lookup&#46;Result/text()[1]', 'varchar(20)')
from @xml.nodes('/Commands/Command') t(c)

but i get the following error: XQuery [value()]: Syntax error near 'Lookup'

How can I escape . (dot in Lookup.Result element tag name) in an XPath expression? Please, help find an elegant solution to this problem. Thank you in advance for your time.

Filip · Accepted Answer · 2013-04-05 12:51:03Z

3

    declare @xml xml 
    set @xml = '<Commands>
    <Command id="1">
    <Lookup.Result>Result.OK</Lookup.Result>
    </Command>
    </Commands> ' 

    select t.c.value('(./Lookup.Result/text())[1]', 'varchar(20)') 
    from @xml.nodes('/Commands/Command') t(c)

you are missing the

()

around the xpath above, MSSQL handles the . fine on my machine

answered Apr 5, 2013 at 12:51

Filip

2,3591 gold badge19 silver badges25 bronze badges

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

1 Comment

Vakhtang Over a year ago

You are right. There are so many combinations when debugging, that I ommited parentheses in one of them...:)) It works now. It may seem that a dot is always interpreted as the current node, but it seems that in some situations it is considered just a text. Thank you.

Ian P · Accepted Answer · 2013-04-05 12:39:05Z

0

Well there are only 5 escape chars:

"   &quot;
'   &apos;
<   &lt;
>   &gt;
&   &amp;

so you need a function to preprocess then and another to post process. i.e. wrap 5 nested REPLACE statements in a udf...

answered Apr 5, 2013 at 12:39

Ian P

1,7241 gold badge10 silver badges12 bronze badges

Collectives™ on Stack Overflow

Select xml nodes that have dot in the name using XPath

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related