XML query () works, value() requires singleton found xdt: untypedAtomic

Tengo un documento xml escrito almacenado como texto. Así que uso CONVERTIR el tipo de datos a xml usando una expresión de tabla Común para poder usar métodos XML: 

WITH xoutput AS (
  SELECT CONVERT(xml, t.requestpayload) 'requestpayload'
    FROM TABLE t
   WHERE t.methodid = 1)
SELECT x.requestpayload.query('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id') as studentid
  FROM xoutput x

La consulta funciona, devolviéndome el elemento. Pero solo estoy interesado en el valor:

WITH xoutput AS (
  SELECT CONVERT(xml, t.requestpayload) 'requestpayload'
    FROM TABLE t
   WHERE t.methodid = 1)
SELECT x.requestpayload.value('declare namespace s="http://blah.ca/api";/s:validate-student-request/s:student-id', 'int') as studentid
  FROM xoutput x

Esto me da el siguiente error:

' value () 'requiere un operando singleton (o secuencia vacía), encontrado de tipo' xdt: untypedAtomic * '

Lo que he buscado en Google dice que el XPATH / XQUERY necesita estar entre paréntesis y / o necesita "[1] " - ninguno ha funcionado. Solo hay un elemento student-id en el xml, aunque supongo que el esquema permite más.

Además, hay numerosos valores de elementos que me gustaría recuperar: ¿hay una forma de declarar el espacio de nombres una vez en lugar de por llamada al método?

Author: Jens Erat, 2009-08-19
Source

3 answers

Necesitas usar esto: 

SELECT 
        x.requestpayload.value('declare namespace s="http://blah.ca/api";
            (/s:validate-student-request/s:student-id)[1]', 'int') 
    AS
        studentid
    FROM 
        xoutput x

Necesita poner su XPath en ( ... ) y agregar un [1] para simplemente seleccionar el primer valor de esa secuencia.

 64
Author: marc_s,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-10-30 18:44:06

Creo que esto también podría hacer:

SELECT 
   x.requestpayload.query('declare namespace s="http://blah.ca/api";
                           /s:validate-student-request/s:student-id').value('.', 'int') 
  as studentid
FROM xoutput x
 8
Author: Ken,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-01-17 11:32:28

Para aquellos interesados en el rendimiento, ejecuté una consulta para comparar estos enfoques y la primera opción con "() y agregar un [1]" fue mucho más rápida que ".consulta ('strFranchise').valor('.',...)".

La diferencia en el plan de ejecución fue del 15% al 85% cuando se ejecutaba uno tras otro con los mismos datos. So () [1] es más de 5 veces más rápido! Plan de ejecución es muy diferente.

 3
Author: David Coster,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-05-13 06:37:46