迭代xml节点SQL Server上的值

Question

我正在尝试提取所有值​​从每个XML节点。例如，对于<Cash>节点，我想恢复值(0,1(，但无法获得

CREATE TABLE #TableXML (Col1 INT PRIMARY KEY, Col2 XML) 
INSERT INTO #TableXML 
VALUES (1,
'<CookedUP>
<Evenement Calcul="16">
<Cookie xmlns="http://services.ariel.morneausobeco.com/2007/02" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AlternateOptionTypeCodes />
<Cash>0</Cash>
<CashInterest>0</CashInterest>
<CashSource>Undefined</CashSource>
<Code>A</Code>
<SmallAmount>0</SmallAmount>
<SmallAmountType>Undefined</SmallAmountType>
</Cookie>
<Cookie xmlns="http://services.ariel.morneausobeco.com/2007/02" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AlternateOptionTypeCodes />
<Cash>1</Cash>
<CashInterest>2</CashInterest>
<CashSource>Undefined</CashSource>
<Code>B</Code>
<SmallAmount>1</SmallAmount>
<SmallAmountType>1</SmallAmountType>
</Cookie>
</Evenement>
</CookedUP> '
)
WITH XMLNAMESPACES ('http://services.ariel.morneausobeco.com/2007/02' AS ns)
SELECT 
b.Col1,
x.XmlCol.value('(ns:Cookie/ns:SmallAmount/text())[2]', 'int') AS SmallAmount,
x.XmlCol.value('(ns:Cookie/ns:Cash/text())[2]', 'int') AS Cash
FROM
#TableXML b
CROSS APPLY 
b.Col2.nodes('CookedUP/Evenement') x(XmlCol);

Answer 1

对您已经拥有的东西进行一个小调整，您就得到了它。名称空间直到Cookie节点才发挥作用。

我将示例更新为一个表变量，尝试一下：

DECLARE @TableXML TABLE(Col1 int primary key, Col2 xml) 
Insert into @TableXML values ( 1,
'<CookedUP>
<Evenement Calcul="16">
<Cookie xmlns="http://services.ariel.morneausobeco.com/2007/02" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AlternateOptionTypeCodes />
<Cash>0</Cash>
<CashInterest>0</CashInterest>
<CashSource>Undefined</CashSource>
<Code>A</Code>
<SmallAmount>0</SmallAmount>
<SmallAmountType>Undefined</SmallAmountType>
</Cookie>
<Cookie xmlns="http://services.ariel.morneausobeco.com/2007/02" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<AlternateOptionTypeCodes />
<Cash>1</Cash>
<CashInterest>2</CashInterest>
<CashSource>Undefined</CashSource>
<Code>B</Code>
<SmallAmount>1</SmallAmount>
<SmallAmountType>1</SmallAmountType>
</Cookie>
</Evenement>
</CookedUP> '
)

;WITH XMLNAMESPACES ('http://services.ariel.morneausobeco.com/2007/02' AS ns)
SELECT b.Col1
--Also include the namespaces here with ns: for each "field" you are after contained within Cookie
,XmlCol.value('(./ns:SmallAmount)[1]', 'int') AS SmallAmount
,XmlCol.value('(./ns:Cash)[1]', 'int') AS Cash
,XmlCol.value('(./ns:Code)[1]', 'nvarchar(10)') AS Code
,XmlCol.value('(./ns:CashSource)[1]', 'nvarchar(10)') AS CashSource
FROM @TableXML b
CROSS APPLY b.Col2.nodes('/CookedUP/Evenement/ns:Cookie') AS x(XmlCol) --ns, the namespace, only at Cookie node.