xpath - SQL Server 2014 - XQuery - get comma-separated List -

xpath - SQL Server 2014 - XQuery - get comma-separated List -

i have database table in sql server 2014 id column (int) , column xmldata of type xml.

this xmldata column contains example:

<book> <title>a nice novel</title> <author>maria</author> <author>peter</author> </book>

as expected, have multiple books, hence multiple rows xmldata.

i want execute query books, peter author. tried in xpath2.0 testers , got conclusion that:

/book/author/concat(text(), if(position() != last())then ',' else '')

works.

if seek port success sql server 2014 express looks this, correctly escaped syntax etc.:

select id books 'peter' in (xmldata.query('/book/author/concat(text(), if(position() != last())then '','' else '''')'))

sql server not seem back upwards construction /concat(...) because of:

the xquery syntax '/function()' not supported.

i @ loss however, why /text() work in:

select id, xmldata.query('/book/author/text()') books

which does.

my constraints:

i bound utilize sql server i bound xpath or else can "injected" statement above (if construction of xml or database changes, xpath above changed isolated , application logic above constructs where clause not touched) see edit

is there way create work?

regards,

billdoor

edit:

my sec constraint boils downwards this:

an application constructs clause by

expression <operator> value(s)

expression stored in database , mapped xmltag eg.:

| tokenname| querystring | "author" | "xmldata.query(/book/author/text())"

the values presented requesting user. if user asks author "peter" operator "equals" application constructs:

xmaldata.query(/book/author/text()) = "peter"

as clause.

if client decides author needs nested in <authors> element, can alter look in construction-database , whole machine keeps running without changes code, manageable.

so need way accomplish that

<xpath> <operator> "peter"

or other combination of 3 isolated components (see above: "peter" in <xpath>...) gets me of peters' books, if there multiple unsorted authors.

this not suffice either (its not sqlserver syntax, idea):

where xmldata.exist('/dossier/client[text() = "$1"]', "peter") = 1;

because operator still nested in expression, not request <> "peter".

i know strange, please don't question concept whole - has history :/

edit: farther clarification:

the filter-rules come app in xml construction basically:

operator: "eq" field: "name" value "peter"

evaluates to:

expression = lookupexpressionforfield("name") --> "table2.xmldata.value('book/author/name[1]', 'varchar')" operator = lookupoperatormapping("eq") --> "=" value = formatvalues("peter") --> "peter" (if multiple values passed formatvalues cosntructs comma seperated list)

the application builds: - constructclause(string expression,string operator,string value)

"table2.xmldata.value('book/author/name[1]', 'varchar')" + "=" + "peter"

then constructs select statement result clause.

it not build this, unescaped, unfiltered injection etc, basic idea.

i can influence how input transalted, meaning can implement methods:

lookupexpressionforfield(string field) lookupoperatormapping(string operator) formatvalues(list<string> values) | formatvalues(string value) constructclause(string expression,string operator,string value)

however take do, can alter parameter types, can freely implement them. less improve of course. constructing comma-seperated list xpath optimal (like if somewhere tick "enable /function()-syntax in xpath" in sqlserver , /concat(if...) work)

how this:

set nocount on; declare @books table (id int not null identity(1, 1) primary key, bookinfo xml); insert @books (bookinfo) values (n'<book> <title>a nice novel</title> <author>maria</author> <author>peter</author> </book>'); insert @books (bookinfo) values (n'<book> <title>another one</title> <author>bob</author> </book>'); select * @books bk bk.bookinfo.exist('/book/author[text() = "peter"]') = 1;

this returns first "book" entry. there can extract portion of xml field using "value" function.

the "exist" function returns boolean / bit. scan through "author" nodes within "book", there no need concat comma-separated list utilize in in list, wouldn't work anyway ;-).

for more info on "value" , "exist" functions, other functions utilize xml data, please see:

xml info type methods

sql-server xpath xquery-sql