Mybatis SELECT Statement for XML xpath query not working

Question

I'm trying to build a myBatis SELECT query on an XML Column (SQL Server DB) with a paramter and can't seem to figure it out

This works:

<select ...>
    SELECT 
        wd.webform_data_xml.value('(/personalDetails//familyName/node())[1]', 'nvarchar(max)') as webform_data_xml,
        wd.webform_data_version_no
    FROM        webform_data_t wd

but when I try to paramterize the Value as such:

<select ...>
    SELECT 
        wd.webform_data_xml.value('#questionXPath:VARCHAR#', 'nvarchar(max)') as webform_data_xml,
        wd.webform_data_version_no
    FROM        webform_data_t wd

I get an unusual error. Any suggestions?

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.

Where have you find examples of #parameter:datatype# usage? AFAIK there's two possible ways to [define MyBatis parameters](http://stackoverflow.com/questions/11760074/mybatis-string-as-parameter) — Arunas Junevicius, Jul 21 '14 at 23:15

score 0 · Answer 1 · answered Jul 23 '14 at 14:33

I'm using an older version when it was called ibatis. Incidentally, I figured out a solution. Basically using an SQL variable to set the elementName and executing as dynamic sql.

<select id="getXMLData" parameterClass="java.lang.String" resultClass="java.lang.String">

  DECLARE @XMLPath varchar(500) 
  SET @XMLPath= #questionXPath:VARCHAR# --"parentNode[1]childNode[1]elementName[1]"

  SET @SQL = 'select webform_data_xml.value(''' + @XMLPath+ ''', ''varchar(100)'')'

  EXEC ( @SQL)

</select>

Mybatis SELECT Statement for XML xpath query not working

1 Answers1