Request full support for position() function in xquery

/*

Right now position() can only be used in predict in xquery. However, in lots of scenarios, the position of the xml data is significant.

*/

DECLARE @x xml;

SET

@x = N‘<SelectAnswers xmlns="http://MyNamespace"&gt;

<AnswerType Type="selectOne" IsRequired="true">

<SelectAnswer>Once a month</SelectAnswer>

<SelectAnswer>Two or three times a month</SelectAnswer>

<SelectAnswer>Usually every week</SelectAnswer>

<SelectAnswer>More than once a week</SelectAnswer>

</AnswerType>

<AnswerType Type="MultipleChoice" IsRequired="true">

<SelectAnswer>This is my first time</SelectAnswer>

<SelectAnswer>Hardly ever/special occasions only</SelectAnswer>

<SelectAnswer>Less than once a month</SelectAnswer>

</AnswerType>

</SelectAnswers>’

 

/*

Suppose you need to get the postion of AnswerType in the xml as AnswerTypeID and the position of SelectAnswer in each AnswerType as AnswerID, you can not use position() to achieve it:

*/

;

WITH XMLNAMESPACES (DEFAULT http://MyNamespace&#8217;)

SELECT

T.answer.value(‘position()’,‘int’) AS AnswerTypeID,

T

.answer.value(‘@Type’, ‘nvarchar(50)’) as AnswerTypeName,

T

.answer.value(‘@IsRequired’, ‘nvarchar(50)’) as IsRequired,

TS

.A.value(‘position()’,‘int’) AS AnswerID,

TS

.A.value(‘.’, ‘nvarchar(50)’) as SelectAnswer

FROM

@x.nodes(‘SelectAnswers/AnswerType’) AS T(answer)

CROSS APPLY T.answer.nodes(‘SelectAnswer’) TS(A)

ORDER

BY AnswerTypeID,AnswerID;

–It gives the following error

/*

Msg 2371, Level 16, State 1, Line 58

XQuery [value()]: ‘position()’ can only be used within a predicate or XPath selector

*/

— There are three workarounds now. One is to use row_number() function:

;

WITH XMLNAMESPACES (DEFAULT http://MyNamespace&#8217;)

SELECT

TAnswerTypes

.AnswerTypeID,

TAnswerTypes

.AnswerTypeName,

TAnswerTypes

.IsRequired,

TS

.A.value(‘.’, ‘nvarchar(50)’) as SelectAnswer,

ROW_NUMBER() OVER(PARTITION BY TAnswerTypes.AnswerTypeID ORDER BY TS.A.value(‘.’, ‘nvarchar(50)’)) AS AnswerID

FROM

(

SELECT ROW_NUMBER() OVER(ORDER BY T.answer.value(‘@Type’, ‘nvarchar(50)’)) AS AnswerTypeID,

T

.answer.value(‘@Type’, ‘nvarchar(50)’) as AnswerTypeName,

T

.answer.value(‘@IsRequired’, ‘nvarchar(50)’) as IsRequired,

T

.answer.query(‘.’) AS xmlAnswer

FROM @x.nodes(‘SelectAnswers/AnswerType’) AS T(answer) ) TAnswerTypes

CROSS APPLY TAnswerTypes.xmlAnswer.nodes(‘AnswerType/SelectAnswer’) TS(A)

ORDER

BY AnswerTypeID,AnswerID;

/*

However, the AnswerTypeID and AnswerID may be different than the postion of the data in the xml because we need to specify the order by column for row_number function.

The second  is to use a number table like master..spt_values (http://www.sqlserverandxml.com/2008/08/tsql-lab-23-retrieving-values-and.html):

*/

;

WITH XMLNAMESPACES (DEFAULT http://MyNamespace&#8217;)

SELECT

TAnswerTypes

.AnswerTypeID,

TAnswerTypes

.AnswerTypeName,

TAnswerTypes

.IsRequired,

TS.A.value(‘.’, ‘nvarchar(50)’) as SelectAnswer,

–ROW_NUMBER() OVER(PARTITION BY TAnswerTypes.AnswerTypeID ORDER BY TS.A.value(‘.’, ‘nvarchar(50)’))

p1.number AS AnswerID

FROM

master..spt_values p1 CROSS APPLY (

SELECT

–ROW_NUMBER() OVER(ORDER BY T.answer.value(‘@Type’, ‘nvarchar(50)’))

p

.number AS AnswerTypeID,

T

.answer.value(‘@Type’, ‘nvarchar(50)’) as AnswerTypeName,

T

.answer.value(‘@IsRequired’, ‘nvarchar(50)’) as IsRequired,

T

.answer.query(‘.’) AS xmlAnswer

FROM master..spt_values p CROSS APPLY @x.nodes(‘SelectAnswers/AnswerType[position()=sql:column("p.number")]’) AS T(answer)

WHERE p.type=N‘P’) TAnswerTypes

CROSS APPLY TAnswerTypes.xmlAnswer.nodes(‘AnswerType/SelectAnswer[position()=sql:column("p1.number")]’) TS(A)

WHERE

p1.type=N‘P’

ORDER

BY AnswerTypeID,AnswerID

/*

The problem is the workaround will be slow for large xml and not convenient for queries of complex xml documents.

One more work around is to count the number of siblings in front of the current node to calculate its position

Its a neat solution but the performance will be bad if the xml is complex*/

;

WITH XMLNAMESPACES (DEFAULT http://MyNamespace&#8217;)

SELECT

T1

.Answer.value(‘1+count(for $a in . return $a/../*[. << $a])’, ‘int’) as AnswerTypeID,

T1

.Answer.value(‘@Type’, ‘nvarchar(50)’) as AnswerTypeName,

T1.Answer.value(‘@IsRequired’, ‘nvarchar(50)’) as IsRequired,

T2

.AnswerType.value(‘.’, ‘nvarchar(50)’) as SelectAnswer,

T2

.AnswerType.value(‘1+count(for $a in . return $a/../*[. << $a])’, ‘int’) as AnswerID

FROM

@x.nodes(‘SelectAnswers/AnswerType’) AS T1(Answer)

CROSS APPLY T1.Answer.nodes(‘SelectAnswer’) T2(AnswerType);

 

/*

If position() can be used in .value method in the first query, it will be a neat,simple, and quick solution.

Note: Sample data/queires are based on a post in MS forum: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=4152191&SiteID=1

I filed a request for this feature at

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383888

Please vote for it if you think it’s a useful feature

*/

Advertisements
This entry was posted in XML, XQuery and XPath. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s