CODEWITHSUNDEEP
sql-serverxpathxquery-sql
billdoor
billdoor

Reputation: 2043

SQL Server 2014 - XQuery - get comma-separated List

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:

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:

evaluates to:

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:

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)

Upvotes: 2

Views: 1318

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

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

Upvotes: 2

Related Questions