We have XML:

<status> <message id="0F051363" /> <state code="DELIVERED" date="02.09.2016 00:00:00">Message has been delivered</state> </status>

I try this:

 select top 100 ms.[stat].value('(status/message id/text())[1]', 'varchar(50)')

Mistake:

 XQuery [dbo.Statuses.stat.value()]: Syntax error near '[', expected a step expression.

How to parse it?

thank

  • four
    [stat].value('(/status/message/@id)[1]', 'char(10)') so request id, [stat].value('(/status/state/text())[1]', 'varchar(50)') so the text. here stackoverflow.com/questions/4835891 / ... more examples - nick_n_a
  • Thank! [Stat] .value ('(/ status / message / @ id) [1]', 'char (10)' option worked

1 answer 1

Attributes are requested as [stat].value('(/status/message/@id)[1]', 'char(10)')

Text is requested as [stat].value('(/status/state/text())[1]', 'varchar(50)')

A few words about the types. If varchar(n) returns a "valid string". char(n) - will add the missing characters with spaces. If int then in case of an error, converting to a number gives an error.

How to check mssql quickly:

 select [stat].query('/status/message'), [stat].value('(/status/state/text())[1]', 'varchar(50)') from (select cast( '<status> <message id="0F051363" /> <state code="DELIVERED" date="02.09.2016 00:00:00">Message has been delivered</state> </status>' as xml) stat) t

Easier option

 declare @x xml set @x = '<a>1</a>' select @x.value('(/a/text())[1]','int')

If you want to request a branch, use [stat].query('/status/message')

Useful links so-En msdn examples