SQL Server 2014 - XQuery - get comma-separated List

Question

I have a database table in SQL Server 2014 with only an ID column (int) and a column xmldata of type XML.

This xmldata column contains for example:

<book>
    <title>a nice Novel</title>
    <author>Maria</author>
    <author>Peter</author>
</book>

As expected, I have multiple books, therefore multiple rows with xmldata.

I now want to execute a query for all books, where Peter is an Author. I tried this in some xPath2.0 testers and got to the conclusion that:

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

works.

If you try to port this success into SQL Server 2014 Express it looks like this, which is correctly escaped syntax etc.:

SELECT id
FROM books
WHERE 'Peter' IN (xmldata.query('/book/author/concat(text(), if(position() != last())then '','' else '''')'))

SQL Server however does not seem to support a construction like /concat(...) because of:

The XQuery syntax '/function()' is not supported.

I am at a loss then however, why /text() would work in:

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

which it does.

My constraints:

• I am bound to use SQL Server
• I am bound to xpath or something else that can be "injected" as the statement above (if the structure of the xml or the database changes, the xpath above could be changed isolated and the application logic above that constructs the Where clause will not be touched) SEE EDIT

Is there a way to make this work?

regards,

BillDoor

EDIT:

My second constraint boils down to this:

An Application constructs the Where clause by

expression <operator> value(s)

expression is stored in a database and is mapped by the xmlTag eg.:

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

the values are presented by the Requesting user. so if the user asks for the author "Peter" with operator "EQUALS" the application constructs:

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

as where clause.

If the customer now decides that author needs to be nested in an <authors> element, i can simply change the expression in the construction-database and the whole machine keeps running without any changes to code, simply manageable.

So i need a way to achieve that

<xPath> <operator> "Peter"

or any other combination of this three isolated components (see above: "Peter" IN <xPath>...) gets me all of Peters' books, even if there are multiple unsorted authors.

This would not suffice either (its not sqlserver syntax, but you get the idea):

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

because the operator is still nested in the expression, i could not request <> "Peter".

I know this is strange, please don't question the concept as a whole - it has a history :/

EDIT: further clarification:

The filter-rules come into the app in an XML structure 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 are passed FormatValues cosntructs a comma seperated list)

the application then builds: - constructClause(String expression,String operator,String value)

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

then constructs a Select statement with the result as WHERE clause.

it does not build it like this, unescaped, unfiltered for injection etc, but this is the basic idea.

i can influence how the input is Transalted, meaning I can implement the methods:

• lookupExpressionForField(String field)
• lookUpOperatorMapping(String operator)
• Formatvalues(List<String> values) | Formatvalues(String value)
• constructClause(String expression,String operator,String value)

however i choose to do, i can change the parameter types, I can freely implement them. The less the better of course. So simply constructing a comma-seperated list with xPath would be optimal (like if i could somewhere just tick "enable /function()-syntax in xPath" in sqlserver and the /concat(if...) would work)

Answer 1

How about something like this:

SET NOCOUNT ON;

DECLARE @Books TABLE (ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY, BookInfo XML);

INSERT INTO @Books (BookInfo)
VALUES (N'<book>
    <title>a nice Novel</title>
    <author>Maria</author>
    <author>Peter</author>
</book>');

INSERT INTO @Books (BookInfo)
VALUES (N'<book>
    <title>another one</title>
    <author>Bob</author>
</book>');

SELECT *
FROM @Books bk
WHERE bk.BookInfo.exist('/book/author[text() = "Peter"]') = 1;

This returns only the first "book" entry. From there you can extract any portion of the XML field using the "value" function.

The "exist" function returns a boolean / BIT. This will scan through all "author" nodes within "book", so there is no need to concat into a comma-separated list only for use in an IN list, which wouldn't work anyway ;-).

For more info on the "value" and "exist" functions, as well as the other functions for use with XML data, please see:

xml Data Type Methods