Using XQuery in SQL Server

Will Mercer · June 2021

I've been writing a custom data list using the SDK toolkit. The data list should return all instances where an ad-hoc or smart query instance are used in a BBIS list and do not contain a specific selection in the parameters / output parameters. I am using XQuery to query the xml fields in the database and this worked perfectly for the QUERYDEFINITIONXML field in ADHOCQUERY, but I've had problems with SMARTQUERYSPEC in SMARTQUERYCATALOG. The problems have arisen because some of the characters have been replaced by their entity reference. This is not true of all xml fields, but it is enough to prevent me being able to use XQuery as far as I can tell.

I've added a screenshot below to show what the specs are like. Support couldn't tell me why the entity references were there in place of the markup delimiters, but I'm hoping one of you might have had some experience of this and can provide some insight / help.

Todd O · June 2021

I don't know why its like this in Smart Query but it was consistent and I was able to get around it using converts and was still able to use XQuery.



declare @SPECXML xml; 
select @SPECXML = SMARTQUERYSPEC from SMARTQUERYCATALOG where ID = '<guid>';

declare @AdHocQuerySaveSmartQueryRequest xml;
select @AdHocQuerySaveSmartQueryRequest = convert(xml, @SPECXML.value('declare namespace d1p1 = "bb_appfx_commontypes"; (//d1p1:MetaTags)[1]', 'nvarchar(max)'), 1);

AdHocQuerySaveSmartQueryRequest

Will Mercer · June 2021

Thanks Todd! That was exactly the sort of solution I was looking for

Using XQuery in SQL Server

Comments

Categories