Skip to content
October 7, 2008 / kiranpatils

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

clip_image002

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..

4 Comments

Leave a Comment
  1. Francis / Nov 20 2008 11:36 am

    It did help me. Thank you very much🙂

  2. Cata / Apr 15 2010 5:47 pm

    Good post. Helped me too, thanks!

  3. LotusShiv / Jul 16 2011 2:13 am

    Say I have the storeproc as
    Create Procedure proc1
    @ParamPath nvarchar(20,
    @ParamFilter nvarchar(20)
    As
    ….

    Is it possible to do something like
    Select ParamValues.InputValue.value(‘.’,’VARCHAR(200)’)
    From @ParamFilter.nodes(@ParamPath) as ParamValues(InputValue)

    … instead of hardcoding the path in the @ParamFilter.nodes(‘…’)

Trackbacks

  1. database development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: