Tengo la siguiente pieza de XML como parte de la columna XML en la tabla, dice MyTable

<policystatusdetail id="XXXXXXXXXXXXXXX">
<CurrentUser>ABCDEFG</CurrentUser>
<LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
<PolicyStatus>Quote-New-Pending</PolicyStatus>
</policystatusdetail>

Quiero extraer solo PolicyStatus como columna.

Estoy usando la consulta a continuación

Select x.r.value('@PolicyStatus','varchar(500)') as PolicyStatus 
from 
(Select cast(XMLData as XML) XMLData from Mytable) s
cross apply s.XMLData.nodes('session/data/policyAdmin/policyStatusdetail') as x(r)

Pero devuelve cero filas. ¿Alguien puede ayudar?

2
Chaitanya 14 dic. 2016 a las 07:24

2 respuestas

La mejor respuesta

Prueba esto:

 DECLARE @xml XML='<policystatusdetail id="XXXXXXXXXXXXXXX">
                     <CurrentUser>ABCDEFG</CurrentUser>
                     <LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
                     <PolicyStatus>Quote-New-Pending</PolicyStatus>
                     </policystatusdetail><policystatusdetail id="XXXXXXXXXXXXXXX">
                     <CurrentUser>ABCDEFG</CurrentUser>
                     <LastModifiedDateTime>2016-04-02T17:03:01.761</LastModifiedDateTime>
                     <PolicyStatus>Quote-New-Pending</PolicyStatus>
                     </policystatusdetail>'

   --Method 1.SELECT  s.b.value('PolicyStatus[1]','varchar(500)') FROM @xml.nodes('policystatusdetail') s(b)
   --Method 2.
   SELECT  s.b.value('.','varchar(500)') FROM @xml.nodes('policystatusdetail/PolicyStatus') s(b)
0
Nolan Shang 14 dic. 2016 a las 05:02

Hay varios defectos:

  • XML es estrictamente sensible a mayúsculas y minúsculas . Tu /policyStatusdetail no puede encontrar <policystatusdetail>

  • Su propia consulta muestra que debe haber más: .nodes('session/data/policyAdmin/policyStatusdetail')

  • El CAST que usa (Select cast(XMLData as XML)) muestra que este XML está, probablemente, almacenado dentro de una columna que no es XML. ¿Cuál es el tipo de datos real?

  • No podemos saber si hay solo una ocurrencia de este nodo. Como el XML es más grande, obviamente, ¿podría haber más?

Si realmente no necesita nada más que la primera aparición de <PolicyStatus>, esto se puede hacer de manera muy simple:

SELECT CAST(XMLData AS XML).value('(//PolicyStatus)[1]','nvarchar(max)') AS PolicyStatus
FROM Mytable

La // al principio activará una búsqueda profunda y encontrará el primer elemento con el nombre de pila ...

2
Shnugo 14 dic. 2016 a las 08:25