Wsrtjtyk

I'm trying to extract all the values ​​from each XML node. For example for the <Cash> node, I would like to recover the value (0,1) but I can not get it

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);

Small tweak to what you already have and you got it.
The namespaces doesn't come into play until the Cookie node.

I update the example to a table variable, give this a try:

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.