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

Question

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

vpolasa

Say Hey Kid

Points: 683
More actions
January 10, 2017 at 12:28 pm

#325176

I'm trying to run below code but it's throwing error. Can someone please point and correct what I'm missing?
drop table emp_mgr
go
create table emp_mgr (emp varchar(99), mgr varchar(99))
insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr select 'Charles','Bob'; ; insert into emp_mgr select 'David','Bob'; insert into emp_mgr select 'Ethan','Bob'; insert into emp_mgr select 'Frank','Charles'; insert into emp_mgr select 'George','Charles'; insert into emp_mgr select 'Henry','Charles'; insert into emp_mgr select 'Ian','David'; insert into emp_mgr select 'James','Ethan'; insert into emp_mgr select 'Ken','Ethan'; insert into emp_mgr select 'Larry','Ethan'; insert into emp_mgr select 'Mike','Frank'; insert into emp_mgr select 'Ned','Frank'; insert into emp_mgr select 'Oliver','Frank'; insert into emp_mgr select 'Peter','Frank'; insert into emp_mgr select 'Quinn','George'; insert into emp_mgr select 'Robert','George'; insert into emp_mgr select 'Scott','George'; insert into emp_mgr select 'Ted','George'; insert into emp_mgr select 'Urban','George'; insert into emp_mgr select 'Victor','Henry'; insert into emp_mgr select 'Wes','Ian'; insert into emp_mgr select 'Xavier','James'; insert into emp_mgr select 'Young','James'; insert into emp_mgr select 'Zach','James';
go
drop function dbo.selectchild
go
create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return
(
select ',"children":[' +
stuff((
select ',{"name":"' +
E.emp + '"'+
isnull(dbo.SelectChild(E.emp), '')+'}'
from dbo.emp_mgr as E
where E.emp = @name
for xml path(''), type
).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end
go
select '{"name":"' + E.emp + '"'+isnull(dbo.SelectChild(E.emp), '')+'}'
from dbo.emp_mgr as E
where E.mgr is null

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

vpolasa (1/10/2017)
I'm trying to run below code but it's throwing error. Can someone please point and correct what I'm missing?
create table emp_mgr (employee varchar(99), manager varchar(99))
insert into emp_mgr select 'Adam',NULL; insert into emp_mgr select 'Bob','Adam'; insert into emp_mgr select 'Charles','Bob'; ; insert into emp_mgr select 'David','Bob'; insert into emp_mgr select 'Ethan','Bob'; insert into emp_mgr select 'Frank','Charles'; insert into emp_mgr select 'George','Charles'; insert into emp_mgr select 'Henry','Charles'; insert into emp_mgr select 'Ian','David'; insert into emp_mgr select 'James','Ethan'; insert into emp_mgr select 'Ken','Ethan'; insert into emp_mgr select 'Larry','Ethan'; insert into emp_mgr select 'Mike','Frank'; insert into emp_mgr select 'Ned','Frank'; insert into emp_mgr select 'Oliver','Frank'; insert into emp_mgr select 'Peter','Frank'; insert into emp_mgr select 'Quinn','George'; insert into emp_mgr select 'Robert','George'; insert into emp_mgr select 'Scott','George'; insert into emp_mgr select 'Ted','George'; insert into emp_mgr select 'Urban','George'; insert into emp_mgr select 'Victor','Henry'; insert into emp_mgr select 'Wes','Ian'; insert into emp_mgr select 'Xavier','James'; insert into emp_mgr select 'Young','James'; insert into emp_mgr select 'Zach','James';
go
create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return
(
select ',"children":[' +
stuff((
select ',{"name":"' +
E.employee + '"'+
isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager = @name
for xml path(''), type
).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end
go
select '{"name":"' + E.employee + '"'+isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager is null

Awesome job posting ddl and sample data. Can you explain what you are trying to retrieve from this query?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vpolasa Say Hey Kid Points: 683 More actions · Answer 2

Thanks a lot for your reply.

I'm trying to create JSON data.

Related to the same topic, could you please also refer to my other post: Hierarchical XML data to JSON in this same forum.

Here is the link: http://www.sqlservercentral.com/Forums/Topic1849353-3077-1.aspx"> http://www.sqlservercentral.com/Forums/Topic1849353-3077-1.aspx

Thanks again..

drew.allen SSC Guru Points: 76996 More actions · Answer 3

It helps if you specify what the error is when you get an error. I ran your code and did not receive an error. My best guess is that, since you did not specify a schema when creating the table, that the table was not created in the dbo schema.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

vpolasa Say Hey Kid Points: 683 More actions · Answer 4

Thanks Drew for your reply. I edited my original post. Could you please retry?

I'm getting below error (sorry for the misleading title):

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Could you also please check my other thread related to same topic: I'm trying to generate JSON data using hierarchical XML data

http://www.sqlservercentral.com/Forums/Topic1849353-3077-1.aspx

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

vpolasa (1/10/2017)
Thanks Drew for your reply. I edited my original post. Could you please retry?
I'm getting below error (sorry for the misleading title):
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Could you also please check my other thread related to same topic: I'm trying to generate JSON data using hierarchical XML data
http://www.sqlservercentral.com/Forums/Topic1849353-3077-1.aspx
Thanks

This is because you have a scalar function that calls itself and your data is more than 32 levels deep. I would rather see this become an inline table valued function but that level of recursion is going to be a challenge no matter what you do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vpolasa Say Hey Kid Points: 683 More actions · Answer 6

Thanks Sean.. I'm not worried about this issue but my main concern is in below thread.

Could you please suggest on how to convert Hierarchical XML data to JSON. I provided more details in my thread: Hierarchical XML data to JSON

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

vpolasa (1/10/2017)
Thanks Sean.. I'm not worried about this issue but my main concern is in below thread.
Could you please suggest on how to convert Hierarchical XML data to JSON. I provided more details in my thread: Hierarchical XML data to JSON

Seems to me that your main concern at the moment is generating the data. Hence this post about the error. Your are receiving that error because the nesting of your data is so deep.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vpolasa Say Hey Kid Points: 683 More actions · Answer 8

In the other thread I mentioned, I'm not getting any error. I'm unable to produce the data as JSON. The sample data I provided in the thread might help you understand the issue I'm facing.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

vpolasa (1/10/2017)
In the other thread I mentioned, I'm not getting any error. I'm unable to produce the data as JSON. The sample data I provided in the thread might help you understand the issue I'm facing.
Thanks

I am so confused. If you don't care about the error why does this thread even exist? This whole thread started out as a problem because you were getting an error. Now it seems you don't care about the error but you are unable to get the data you want. Wouldn't the error kind of be part of the reason? Maybe there is something I am missing here but we seem to be going around in circles.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

vpolasa Say Hey Kid Points: 683 More actions · Answer 10

I'm very sorry for all the confusion I might have created. My original thread: 'Hierarchical XML data to JSON' is still unanswered. I have been trying other ways to get the output and created this thread.

I request you to look at my first thread: Hierarchical XML data to JSON and add your suggestions.

Thanks