Leer valores de un campo XML de una tabla en SQL 2008 server

Question

Saludos

Tengo una tabla de documentos por pagar (DOCUM_CP), pero su programacion de pago (fecha y monto) se encuentra en el campo PRO_PAGO, que los contiene en un XML.

El contenido del campo es el siguiente:

<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>  <VFPDataSet>  
<xsd:schema id="VFPDataSet" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">  
 <xsd:element name="VFPDataSet" msdata:IsDataSet="true">
 <xsd:complexType>      <xsd:choice maxOccurs="unbounded">     
 <xsd:element name="TempCursorXML" minOccurs="0" maxOccurs="unbounded">
 <xsd:complexType>         <xsd:attribute name="fecha" type="xsd:date" use="required"/>
        <xsd:attribute name="monto" use="required">    
  <xsd:simpleType>           <xsd:restriction base="xsd:decimal">            <xsd:totalDigits value="17"/>  
         <xsd:fractionDigits value="2"/>   
   </xsd:restriction>        
   </xsd:simpleType>     
   </xsd:attribute>     
   </xsd:complexType>   
   </xsd:element>
     </xsd:choice>      <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax"/>   
  </xsd:complexType>    </xsd:element>   </xsd:schema> 
 <TempCursorXML fecha="2017-04-07" monto="65147290.29"/>  </VFPDataSet> 

---------------------------------

Estoy haciendo estas pruebas:

declare @PPAGO as xml
set @PPAGO=(select pro_pago from docum_cp where co_cli='860502609' and nro_doc=481)

-- Donde en teoria estoy metiendo en la variable @PPAgo el contenido del campo PRO_PAGO de la table

-- Luego quiero extraer de esa variable, los campos fecha y monto. Comienzo con la fecha

select 
  x.value('@fecha','date') as Pfecha
from @PPAGO.nodes('')  PPAGO (X)

Y obtengo el siguiente mensaje

Mens. 6306, Nivel 16, Estado 1, Línea 4
Se ha pasado una expresión XQuery no válida al método de tipo de datos XML.

Confieso que nunca he trabajado con XML y bueno, esto me esta costando bastante.

Agradezco la orientación

Answer 1

Saludos,

Viendo tu XML debes de revisar los nodos que este tiene antes de comenzar a leerlo, lo he tratado de reorganizar un poco para entenderlo y a continuación adjunto como debes de hacer la lectura:

declare @PPAGO xml;

set @PPAGO =
'<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>  
<VFPDataSet>   
	<xsd:schema id="VFPDataSet" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   
	<xsd:element name="VFPDataSet" msdata:IsDataSet="true">
	<xsd:complexType>      
		<xsd:choice maxOccurs="unbounded">      
			<xsd:element name="TempCursorXML" minOccurs="0" maxOccurs="unbounded"> 
			<xsd:complexType>         <xsd:attribute name="fecha" type="xsd:date" use="required"/> 
					<xsd:attribute name="monto" use="required">     
			<xsd:simpleType>           <xsd:restriction base="xsd:decimal">            <xsd:totalDigits value="17"/>   
					<xsd:fractionDigits value="2"/>    
			</xsd:restriction>         
			</xsd:simpleType>      
			</xsd:attribute>      
			</xsd:complexType>    
			</xsd:element> 
		</xsd:choice>      <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax"/>    
	</xsd:complexType>    </xsd:element>   </xsd:schema>  
	<TempCursorXML fecha="2017-04-07" monto="65147290.29"/>  
 </VFPDataSet>'

SELECT 
	b.x.value('@fecha','date') as PFecha,
	b.x.value('@monto','decimal') as PMonto
FROM
	@PPAGO.nodes('/VFPDataSet')  as a(x)
	OUTER APPLY
	a.x.nodes('TempCursorXML') as b(x)

Prueba así, si tienes alguna duda solo preguntas.

---

Ayacucho - Perú
Recuerda si mi solución atiende tu consulta por favor márcala como útil y como respuesta.

http://litigiouslobo.blogspot.com/
El Blog de Steve Morrison

Answer 2

Si el numero de elementos TempCursorXML es siempre uno, entonces tendras mejor desempenio refiriendote al camino completo de cada atributo.

DECLARE @T table (
col1 int NOT NULL IDENTITY PRIMARY KEY,
col2 xml
);

INSERT INTO @T
        (col2)
VALUES
        ('<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPDataSet>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="VFPDataSet">
    <xsd:element name="VFPDataSet" msdata:IsDataSet="true">
      <xsd:complexType>
        <xsd:choice maxOccurs="unbounded">
          <xsd:element name="TempCursorXML" minOccurs="0" maxOccurs="unbounded">
            <xsd:complexType>
              <xsd:attribute name="fecha" type="xsd:date" use="required" />
              <xsd:attribute name="monto" use="required">
                <xsd:simpleType>
                  <xsd:restriction base="xsd:decimal">
                    <xsd:totalDigits value="17" />
                    <xsd:fractionDigits value="2" />
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:attribute>
            </xsd:complexType>
          </xsd:element>
        </xsd:choice>
        <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax" />
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <TempCursorXML fecha="2017-04-07" monto="65147290.29" />
</VFPDataSet>');

INSERT INTO @T
        (col2)
VALUES
        ('<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
<VFPDataSet>
  <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="VFPDataSet">
    <xsd:element name="VFPDataSet" msdata:IsDataSet="true">
      <xsd:complexType>
        <xsd:choice maxOccurs="unbounded">
          <xsd:element name="TempCursorXML" minOccurs="0" maxOccurs="unbounded">
            <xsd:complexType>
              <xsd:attribute name="fecha" type="xsd:date" use="required" />
              <xsd:attribute name="monto" use="required">
                <xsd:simpleType>
                  <xsd:restriction base="xsd:decimal">
                    <xsd:totalDigits value="17" />
                    <xsd:fractionDigits value="2" />
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:attribute>
            </xsd:complexType>
          </xsd:element>
        </xsd:choice>
        <xsd:anyAttribute namespace="http://www.w3.org/XML/1998/namespace" processContents="lax" />
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <TempCursorXML fecha="2017-04-08" monto="65147296.23" />
</VFPDataSet>');

SELECT
	T1.col1,
	T2.fecha,
	T2.monto	
FROM
	@T AS T1
	CROSS APPLY
    (
	SELECT
		T1.col2.value('(VFPDataSet/TempCursorXML/@fecha)[1]', 'date') AS fecha,
		T1.col2.value('(VFPDataSet/TempCursorXML/@monto)[1]', 'decimal(17, 2)') AS monto
	) AS T2
GO

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 3

Saludos

Esta aclarado.

Revisando sus respuestas, no estaba lejos de la solución.

T1.col2.value('(VFPDataSet/TempCursorXML/@fecha)[1]', 'date') AS fecha,

Me faltaba el valor       VFPDataSet/TempCursorXML/  

Lo coloque y me funciono la extraccion de datos.

Muchisimas gracias por la ayuda

---

Rafael Rangel Consultor Sistema de Gestión Empresarial www.service-consulting.com.co