SQL Server 2008 R2：仅在使用XQuery（.value）查询XML时才获取NULL

My table is called Table_1 with 2 columns:

我的表名为Table_1,包含2列:

id (int) and myxml (xml)

I attached the xml snapshot I am working with below. (This xml corresponded to id = 1)

我附上了我正在使用的xml快照。 (此xml对应于id = 1)

The problem I have is that I only get NULL when using XQuery (.value) to query into this XML

我遇到的问题是,当使用XQuery(.value)查询此XML时,我只获得NULL

This is the code I wrote:

这是我写的代码:

SELECT myXml.value('(Archivebatch/Cr/Modifcltn/Modif)[2]/@modiftype','int')
 from Table_1 where id = 1

I only get NULL no matter what I tried to query out of the xml I have also try including namespace which doesn't help (still only return NULL)

我只得到NULL,无论我试图从xml中查询出来我还尝试包含名称空间,但这只会返回NULL

EDIT: I tried to include namespace by:

编辑:我试图通过以下方式包含命名空间:

WITH XMLNAMESPACES ('w3.org/2001/XMLSchema-instance' AS ns) 
select myXml.value(' 
(/ns:Archivebatch/ns:Cr/ns:Modifcltn/ns:Modif)[2]/@modiftype','int') 
from dbo.table_1 where id = 1 

1 个解决方案

#1

The namespace that matter for this particular query is the default namespace defined here :

此特定查询的重要名称空间是此处定义的默认名称空间:

xmlns="SIMATIC_BATCH_V8_1_0"

so you should've registered ns prefix to point to the above namespace URI, or you can register default namespace using ;WITH XMLNAMESPACES and omit prefixes from the xquery as it becomes default now :

所以你应该注册ns前缀指向上面的命名空间URI,或者你可以使用; WITH XMLNAMESPACES注册默认命名空间,并从xquery中省略前缀,因为它现在变为默认值:

;WITH XMLNAMESPACES (DEFAULT 'SIMATIC_BATCH_V8_1_0')
select myXml.value('(/Archivebatch/Cr/Modifcltn/Modif)[2]/@modiftype','int') 
from dbo.table_1 where id = 1