How to parse xml data in sql server dynamically

Question

I am trying to get a node value of 'customer' but I need to pick this node dynamically and get the value of it because I am trying to get primary key node value and I will get that primary key from other table for now I have set the variable @primary ='CUSTOMER' but I am getting error like

The data types varchar and xml are incompatible in the add operator.

I tried to use cast but no use. Can anyone please help me on this

    declare @var xml,
@var1 varchar(max),
@var2 varchar(max),
@var3 varchar(max),
@var4 varchar(max),
@var5 varchar(max),
@primary  varchar(max);
set @primary='CUSTOMER';
set @var='<RequestData>
   <CREATED_BY>nachagon</CREATED_BY>
   <CUSTOMER_TYPE />
   <modalid>editmodgrid_iBase_VW_Customers</modalid>
   <Input_Date_From>31-Dec-2007 07:30:00 PM</Input_Date_From>
   <Timestamp>26-Mar-2019 04:02:01 PM</Timestamp>
   <UPDATED_ON />
   <USER_SELECTED_TIMEZONE>Venezuela Standard Time</USER_SELECTED_TIMEZONE>
   <NAME>Kevin Good</NAME>
   <CITY>Stewartsville</CITY>
   <COUNTRY>US</COUNTRY>
   <Input_Date_To>29-Jun-2008 07:30:00 PM</Input_Date_To>
   <UPDATED_BY>nachagon</UPDATED_BY>
   <CREATED_ON>28-Mar-2019 11:57:46 AM</CREATED_ON>
   <CUSTOMER>0000000233</CUSTOMER>
   <oper>edit</oper>
   <id>jqg1</id>
   <tablename>iBase_VW_Customers</tablename>
   <moduleId>Customers</moduleId>
   <LOGGED_IN_USER_ID>11</LOGGED_IN_USER_ID>
</RequestData>'
select  @var1=coalesce(@var1 + ',','')+NodeName , @var2=coalesce(@var2 +',','')+NodeValue 
from (select NodeName,NodeValue from(SELECT  NodeName = C.value('local-name(.)', 'varchar(50)'),
NodeValue = C.value('(.)[1]', 'varchar(50)')  FROM @var.nodes('/RequestData/*') AS T(C))t2  WHERE t2.NodeName NOT IN ('CREATED_BY', 'CREATED_ON', 'id','LOGGED_IN_USER_ID','modalid', 'moduleId','oper','tablename','UPDATED_BY','UPDATED_ON','USER_SELECTED_TIMEZONE'))t
select @var1,@var2 

SET @var5= 'select '+@var+'.value(''(RequestData/'+@primary+')[1]'',''varchar(max)'')'
exec (@var5)
print(@var5)

Coming back on the error itself, you are combining both XML an varchar when you set var 5... @var is XML while you add it to a varchar — kcotman
– kcotman, Commented Apr 1, 2019 at 13:47
yeah I got that issue since I am very bad at casting asking for help — pythonCoder
– pythonCoder, Commented Apr 1, 2019 at 13:48

kcotman · Accepted Answer · 2019-04-01 14:18:09Z

This example might help

declare @primary nvarchar(max) = 'CUSTOMER';
declare @var xml =
'<RequestData>
   <CREATED_BY>nachagon</CREATED_BY>
   <CUSTOMER_TYPE />
   <modalid>editmodgrid_iBase_VW_Customers</modalid>
   <Input_Date_From>31-Dec-2007 07:30:00 PM</Input_Date_From>
   <Timestamp>26-Mar-2019 04:02:01 PM</Timestamp>
   <UPDATED_ON />
   <USER_SELECTED_TIMEZONE>Venezuela Standard Time</USER_SELECTED_TIMEZONE>
   <NAME>Kevin Good</NAME>
   <CITY>Stewartsville</CITY>
   <COUNTRY>US</COUNTRY>
   <Input_Date_To>29-Jun-2008 07:30:00 PM</Input_Date_To>
   <UPDATED_BY>nachagon</UPDATED_BY>
   <CREATED_ON>28-Mar-2019 11:57:46 AM</CREATED_ON>
   <CUSTOMER>0000000233</CUSTOMER>
   <oper>edit</oper>
   <id>jqg1</id>
   <tablename>iBase_VW_Customers</tablename>
   <moduleId>Customers</moduleId>
   <LOGGED_IN_USER_ID>11</LOGGED_IN_USER_ID>
</RequestData>'


DECLARE @cmd NVARCHAR(MAX) = 'SELECT @xml.value(''(/RequestData/'+@primary+')[1]'', ''nvarchar(max)'' )'
EXECUTE sp_executesql @cmd, N'@xml XML', @xml =  @var

It's not 100% bullet proof but it might help you achieve your goal. Also, if you know the datatype I recommend to pass this also to the dynamic query.

Collectives™ on Stack Overflow

How to parse xml data in sql server dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related