I need to read the value of node <IdChk> with the following code:
DECLARE @XML AS XML
SET @XML ='
<ListChk >
<IdChk>26</IdChk>
<IdChk>29</IdChk>
<IdChk>35</IdChk>
<obs>test</obs>
<Fol>10944</Fol>
</ListChk>'
SELECT Y.V.value('IdChk[1]','int') AS chk
FROM @XML.nodes('/ListChk') X(V)
CROSS APPLY X.V.nodes('IdChk') Y(V)
but the value this query return is null
What could be the problem?
Unless you are actually using something on the root element you can shorten your query like this...
SELECT Y.V.value('.','int') AS chk
FROM @XML.nodes('/ListChk/IdChk') Y(V)
... (even then you could use xpath to navigate to parent nodes with .value
SELECT
Y.V.value('.','int') AS chk
,Y.V.value('../obs[1]','varchar(4)') AS obs
,Y.V.value('../Fol[1]','int') AS Fol
FROM @XML.nodes('/ListChk/IdChk') Y(V)
chk obs Fol
26 test 10944
29 test 10944
35 test 10944
Your XQuery already selects the <IdChk> nodes - you don't need to (and must not) use the .value('IdChk[1]', 'int') to try and grab the value.
Instead, you need to just read out the value of those nodes like this:
SELECT
Y.V.value('.', 'int') AS chk
FROM
@XML.nodes('/ListChk') X(V)
CROSS APPLY
X.V.nodes('IdChk') Y(V)
