在SQL Server 2012中读取XML

I need to read the value of node <IdChk> with the following code:

我需要使用以下代码读取node 的值：

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

但此查询返回的值为null

What could be the problem?

可能是什么问题呢？

2 个解决方案

#1

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

...（即使这样你也可以使用xpath导航到带有.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

#2

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.

您的XQuery已经选择了节点 - 您不需要（也不能）使用.value（'IdChk [1]'，'int'）来尝试获取值。

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)

#1