Fetching value from XML parameter in SP-SQL SERVER 2005

XML Document as a parameter

[sourcecode language='xml']

DECLARE @xmlParam as XML

SET @xmlParam =

‘<Root>

<List>

<ID>1</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>2</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>3</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>13</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>14</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>15</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>16</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

<List>

<ID>17</ID>

<Cost>10.00</Cost>

<Active>true</Active>

</List>

</Root>’

[/sourcecode]

SQL Syntax for retrieving values:

SELECT

–get the ID

ParamValues.Data.value(‘ID[1]‘,’bigint’) as ID,

–get the Cost

ParamValues.Data.value(‘Cost[1]‘,’decimal(10,2)’) as Cost,

ParamValues.Data.value(‘Active[1]‘,’bit’) as Active –Active

FROM

–get the data from entityfields using Xquery

@xmlParam.nodes(‘Root/List’) as ParamValues(Data)

Output

Updated on 10-Oct-2008

At a time of Update…How to fetch values??

DECLARE @employeesData as XML

SET @employeesData =
‘<Employees>
<Employee>
<EmployeeID>1</EmployeeID>
<EmployeeName>ABC</EmployeeName>
</Employee>
<Employee>
<EmployeeID>2</EmployeeID>
<EmployeeName>XYZ</EmployeeName>
</Employee>
</Employees>’

UPDATE Employees
SET [EmployeeName] = ParamValues.Data.value(‘EmployeeName[1]‘,’varchar(50)’)
FROM @employeesData .nodes(‘/Employees/Employee’) as ParamValues(Data)
WHERE EmployeeID IN (ParamValues.Data.value(‘EmployeeID[1]‘,’bigint’))

Hope it helps some one…..runing out of time..

EXECUTE permission denied on object ‘ABC’, database ‘XYZ’, schema ‘dbo’.

Error:

EXECUTE permission denied on object ‘usp_GetEmployeeDetails’, database ‘Employee’, schema ‘dbo’.

Solution:

1. Expand your database Node. For me it’s “Employee”.
2. Underneath of it Expand “Security” Node.
3. Click on the user which you are using for Database operations. By default it is

MACHINENAME\ASPNET [MYMACHINE\ASPNET].

1. It will open the window as shown below from database Roles Select db_owner:

That’s it…