CODEWITHSUNDEEP
sql-serverxml
Ilyas
Ilyas

Reputation: 153

extracts value from xml nodes

I am trying to extracts some values and colonnes from xml nodes but it is working . this is my code

Drop table #TableXML 
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>
  </Evenement>
</CookedUP> '
)




SELECT  b.Col1,
        x.XmlCol.value('(Cookie/SmallAmount)[1]','VARCHAR(100)') AS SmallAmount,
        x.XmlCol.value('(Cookie/cash)[1]','VARCHAR(100)') AS cash
FROM    #TableXML b
CROSS APPLY b.Col2.nodes('/CookedUP/Evenement') x(XmlCol)

when i ran this query , i am getting null values.

Upvotes: 1

Views: 48

Answers (1)

Thom A
Thom A

Reputation: 95554

Your node Cookie has a namespace, so you need to declare that and and use it as part of your path (as it isn't the default namespace). Also, you had cash instead of Cash (xQuery is case sensitive). Thus you get:

WITH XMLNAMESPACES ('http://services.ariel.morneausobeco.com/2007/02' AS ns)
SELECT b.Col1,
       x.XmlCol.value('(ns:Cookie/ns:SmallAmount/text())[1]', 'int') AS SmallAmount,
       x.XmlCol.value('(ns:Cookie/ns:Cash/text())[1]', 'int') AS Cash
FROM #TableXML b
     CROSS APPLY b.Col2.nodes('CookedUP/Evenement') x(XmlCol);

I also changed the datatype to int (this may not be the correct datatype, you might want decimal), as the data is clearly numerical and added /text() to the values (I can't remember the exact reason, but the use of /text() has a performance benefit).

Upvotes: 2

Related Questions