[Solved] Why is this cursor giving an error?

by


Given an input XML, you can use this simple XQuery statement to “shred” the XML into relational rows and columns – just do a simple INSERT INTO ..... and you’re done. No messy cursor nor OPENXML stuff needed…

DECLARE @input XML = '<Salaray>
  <TransactionSalary 
    EmpSL="2" Basic="9860" Grad_pay="4100.00" DA="6282.00" HRA="2094" MA="300.00" Ptax="150.00" pf="2000" 
    Itax="0.00" LIC="0.00" Month_Of="14/Dec/2012" /> 
  <TransactionSalary EmpSL="1" Basic="12560" Grad_pay="4800.00" DA="7812.00" HRA="2604.00" MA="300" Ptax="150.00" pf="2000" Itax="0.00" LIC="0.00" Month_Of="14/Dec/2012" /> 
  <TransactionSalary EmpSL="4" Basic="11850" Grad_pay="4800.00" DA="7493.00" HRA="2498.00" MA="300" Ptax="150.00" pf="2000" Itax="0.00" LIC="0.00" Month_Of="14/Dec/2012" /> 
  </Salaray>'

/* just add this INSERT INTO statement when you're confident the SELECT returns the right data! 
INSERT INTO [Monthly_Salary_Statement]([Emp_SL], [Basic], [Grad_pay], [DA], [HRA], [MA], [Ptax], [Itax], [pf],  [LIC], [Month_Of])
*/
 SELECT
    Emp_SL = XTbl.TSal.value('@EmpSL', 'int'),
    [Basic] = XTbl.TSal.value('@Basic', 'decimal(10,2)'),
    Grad_pay = XTbl.TSal.value('@Grad_pay', 'decimal(6,2)'),
    DA = XTbl.TSal.value('@DA', 'decimal(6,2)'),
    HRA = XTbl.TSal.value('@HRA', 'decimal(6,2)'),
    MA = XTbl.TSal.value('@MA', 'decimal(6,2)'),
    Ptax = XTbl.TSal.value('@Ptax', 'decimal(6,2)'),
    Itax = XTbl.TSal.value('@Itax', 'decimal(6,2)'),
    pf = XTbl.TSal.value('@pf', 'decimal(6,2)'),
    LIC = XTbl.TSal.value('@LIC', 'decimal(6,2)'),
    Month_Of = XTbl.TSal.value('@Month_Of', 'datetime')
  FROM
    @input.nodes('/Salaray/TransactionSalary') AS XTbl(TSal)

Also: mind you, you have values like 12560 for your Basic attribute – those will NOT fit into a decimal(6,2) – that’s 6 digits total, 2 of which after the comma – which also means: only 4 digits before the comma – not enough to hold 12560. I used decimal(10,2) instead – that works.

Gives me output of:

enter image description here

solved Why is this cursor giving an error?