如何进行 XQuery - tsql

Question

我得到了这个 XML。

<ArrayOfGuid xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <guid>475B07C8-CDEA-4000-BAAE-485336190E10</guid>
     <guid>6E5B87BC-CF80-4AB2-939E-ED951FA604AB</guid>
     <guid>31BFDA60-3BB2-49DC-8C69-10F6E1B540CA</guid>
</ArrayOfGuid>

需要得到类似这样的结果：

BatchGUID
475B07C8-CDEA-4000-BAAE-485336190E10
6E5B87BC-CF80-4AB2-939E-ED951FA604AB
31BFDA60-3BB2-49DC-8C69-10F6E1B540CA

不知何故，我陷入了

DECLARE @BatchGUID XML

SET @BatchGUID = '<ArrayOfGuid xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                      <guid>475B07C8-CDEA-4000-BAAE-485336190E10</guid>
                      <guid>6E5B87BC-CF80-4AB2-939E-ED951FA604AB</guid>
                      <guid>31BFDA60-3BB2-49DC-8C69-10F6E1B540CA</guid>
                    </ArrayOfGuid>'

DECLARE @t_xml TABLE
(
    BatchGUID XML
)

INSERT INTO @t_xml
SELECT @BatchGUID

SELECT C.* FROM @t_xml
cross apply (select name.value('guid[1]', 'uniqueidentifier') as [BatchGUID] 
from BatchGUID.nodes('//ArrayOfGuid/guid') as c(name)) as C

This is returned me

BatchGUID
NULL
NULL
NULL

需要知道我在这里做错了什么。

谢谢，

Answer 1

你们是如此非常接近！

您的问题是您正在从 guid 节点中选择 guid[1] 节点的值。当然，guid 节点不包含任何子节点 - 您希望从该节点本身选择值（表示为 .）

SELECT C.* FROM @t_xml
cross apply (select name.value('.', 'uniqueidentifier') as [BatchGUID]
from @BatchGUID.nodes('//ArrayOfGuid/guid') as c(name)) as C