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

  • Pregunta

  • 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

    lunes, 24 de abril de 2017 16:32

Respuestas

  • 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

    viernes, 5 de mayo de 2017 17:35

Todas las respuestas

  • 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

    viernes, 5 de mayo de 2017 16:12
  • 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

    viernes, 5 de mayo de 2017 17:35
  • 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

    jueves, 11 de mayo de 2017 19:51