none
Ayuda con Lectura de XML con Namespace RRS feed

  • Pregunta

  • Hola amigos de la comunidad, verán, tenemos XML que previamente son validados con un Esquema XSD, la cabecera de nuestros XML tiene el xmlns del Esquema asociado, una vez validados se debe de leer su contenido, el problema surge al tener el namespace xmlns no es reconocido los campos al intentar leerlos, si se borra el xmlsn se lee los datos pero no reconoce al nodo OBInfoLow, además este XML tiene varios Header, como leerlos? alguna idea amigos?, Este es el XML.

    declare @DocumentoXML nvarchar(max);
    declare @X xml;
    set @DocumentoXML = '
     <GoldFields xmlns="GoldFields">
     <OBMessageHeader 
    	action="Process"/>
    	<OutboundLoadInformation 
    			OrderNumber="02-0383" 
    			trailerNo="">
    			<OBDetail>
    				<OBAction>
    					<OBDate 
    						year="2013" 
    						day="26" 
    						month="02" 
    						hour="14" 
    						min="00" 
    						local="-2" 
    						timeZone="MST"/>
    				</OBAction>
    				<OBInfoLow 
    					InfoName="OUTBOUND" 
    					infoId=""/>
    			</OBDetail>
    			<OBDetail>
    				<OBAction>
    					<OBDate 
    						year="2013" 
    						day="28" 
    						month="02" 
    						hour="14" 
    						min="00" 
    						local="-1" 
    						timeZone="CST"/>
    				</OBAction>
    			</OBDetail>
    	</OutboundLoadInformation>
    	<ProcessInformation 
    		prov="RDYH" 
    		routine="EXPRESS"/>
    	<ShipperCosts 
    		totalFreightCost="">
    		<CostElement 
    			costType="LINE" 
    			unitCost="150.00000" 
    			unitType="FL" 
    			quantity="1.00000" 
    			totalCost="150.00"/>
    		<CostElement 
    			costType="South" 
    			unitCost="0.46000" 
    			unitType="MI" 
    			quantity="695.00000" 
    			totalCost="319.70"/>
    		<CostElement 
    			costType="TAX" 
    			unitCost="0.04990" 
    			unitType="PIC" 
    			quantity="469.70000" 
    			totalCost="23.44"/>
    	</ShipperCosts>
    </GoldFields>
    ';
    
    set @X = @DocumentoXML;
    
    SELECT
        A.n.value('@action[1]','VARCHAR(20)') AS [action],
        A.n.value('@prov[1]','VARCHAR(20)') AS [prov],
        A.n.value('@routine[1]','VARCHAR(20)') AS [routine],
        A.n.value('@totalFreightCost[1]','VARCHAR(20)') AS [totalFreightCost]
    FROM @x.nodes('/*/*') A(n);
    
    
    
    SELECT	
        B.n.value('@OrderNumber[1]','VARCHAR(20)') AS [OrderNumber],
        B.n.value('@trailerNo[1]','VARCHAR(20)') AS [trailerNo],
        E.n.value('@year[1]','VARCHAR(20)') AS [year],
        E.n.value('@day[1]','VARCHAR(20)') AS [day],
        E.n.value('@month[1]','VARCHAR(20)') AS [month],
        E.n.value('@hour[1]','VARCHAR(20)') AS [hour],
        E.n.value('@min[1]','VARCHAR(20)') AS [min],
        E.n.value('@local[1]','VARCHAR(20)') AS [local],
        E.n.value('@timeZone[1]','VARCHAR(20)') AS [timeZone],
        D.n.value('@InfoName[1]','VARCHAR(20)') AS [InfoName],
        D.n.value('@infoId[1]','VARCHAR(20)') AS [infoId]       
    FROM @x.nodes('/GoldFields') A(n)
    OUTER APPLY
    A.n.nodes('OutboundLoadInformation') AS B(n)
    OUTER APPLY
    B.n.nodes('OBDetail') AS C(n)
    OUTER APPLY
    C.n.nodes('OBAction') AS D(n)
    OUTER APPLY
    D.n.nodes('OBDate') AS E(n);  
    
    

    Saludos cordiales,

    lunes, 11 de marzo de 2013 16:52

Respuestas

  • Trata:

    USE tempdb;
    GO
    declare @X xml = '<GoldFields xmlns="GoldFields">
    	<OBMessageHeader action="Process"/>
    	<OutboundLoadInformation OrderNumber="02-0383" trailerNo="">
    		<OBDetail>
    			<OBAction>
    				<OBDate year="2013" day="26" month="02" hour="14" min="00" local="-2" timeZone="MST"/>
    			</OBAction>
    			<OBInfoLow InfoName="OUTBOUND" infoId=""/>
    		</OBDetail>
    		<OBDetail>
    			<OBAction>
    				<OBDate year="2013" day="28" month="02" hour="14" min="00" local="-1" timeZone="CST"/>
    			</OBAction>
    		</OBDetail>
    	</OutboundLoadInformation>
    	<ProcessInformation prov="RDYH" routine="EXPRESS"/>
    	<ShipperCosts totalFreightCost="">
    		<CostElement costType="LINE" unitCost="150.00000" unitType="FL" quantity="1.00000" totalCost="150.00"/>
    		<CostElement costType="South" unitCost="0.46000" unitType="MI" quantity="695.00000" totalCost="319.70"/>
    		<CostElement costType="TAX" unitCost="0.04990" unitType="PIC" quantity="469.70000" totalCost="23.44"/>
    	</ShipperCosts>
    </GoldFields>';
    
    WITH XMLNAMESPACES (DEFAULT 'GoldFields')
    SELECT	
        B.n.value('@OrderNumber[1]','VARCHAR(20)') AS [OrderNumber],
        B.n.value('@trailerNo[1]','VARCHAR(20)') AS [trailerNo],
        E.n.value('@year[1]','VARCHAR(20)') AS [year],
        E.n.value('@day[1]','VARCHAR(20)') AS [day],
        E.n.value('@month[1]','VARCHAR(20)') AS [month],
        E.n.value('@hour[1]','VARCHAR(20)') AS [hour],
        E.n.value('@min[1]','VARCHAR(20)') AS [min],
        E.n.value('@local[1]','VARCHAR(20)') AS [local],
        E.n.value('@timeZone[1]','VARCHAR(20)') AS [timeZone],
        I.n.value('@InfoName[1]','VARCHAR(20)') AS [InfoName],
        I.n.value('@infoId[1]','VARCHAR(20)') AS [infoId]       
    FROM 
    	@x.nodes('GoldFields') A(n)
    	OUTER APPLY
    	A.n.nodes('OutboundLoadInformation') AS B(n)
    	OUTER APPLY
    	B.n.nodes('OBDetail') AS C(n)
    	OUTER APPLY
    	C.n.nodes('OBAction') AS D(n)
    	OUTER APPLY
    	D.n.nodes('OBDate') AS E(n)
    	OUTER APPLY
    	C.n.nodes('OBInfoLow') AS I(n);
    GO
    /*
    
    OrderNumber	trailerNo	year	day	month	hour	min	local	timeZone	InfoName	infoId
    02-0383		2013	26	02	14	00	-2	MST	OUTBOUND	
    02-0383		2013	28	02	14	00	-1	CST	NULL	NULL
    
    */


    AMB

    Some guidelines for posting questions...

    lunes, 11 de marzo de 2013 18:22

Todas las respuestas

  • Trata:

    USE tempdb;
    GO
    declare @X xml = '<GoldFields xmlns="GoldFields">
    	<OBMessageHeader action="Process"/>
    	<OutboundLoadInformation OrderNumber="02-0383" trailerNo="">
    		<OBDetail>
    			<OBAction>
    				<OBDate year="2013" day="26" month="02" hour="14" min="00" local="-2" timeZone="MST"/>
    			</OBAction>
    			<OBInfoLow InfoName="OUTBOUND" infoId=""/>
    		</OBDetail>
    		<OBDetail>
    			<OBAction>
    				<OBDate year="2013" day="28" month="02" hour="14" min="00" local="-1" timeZone="CST"/>
    			</OBAction>
    		</OBDetail>
    	</OutboundLoadInformation>
    	<ProcessInformation prov="RDYH" routine="EXPRESS"/>
    	<ShipperCosts totalFreightCost="">
    		<CostElement costType="LINE" unitCost="150.00000" unitType="FL" quantity="1.00000" totalCost="150.00"/>
    		<CostElement costType="South" unitCost="0.46000" unitType="MI" quantity="695.00000" totalCost="319.70"/>
    		<CostElement costType="TAX" unitCost="0.04990" unitType="PIC" quantity="469.70000" totalCost="23.44"/>
    	</ShipperCosts>
    </GoldFields>';
    
    WITH XMLNAMESPACES (DEFAULT 'GoldFields')
    SELECT	
        B.n.value('@OrderNumber[1]','VARCHAR(20)') AS [OrderNumber],
        B.n.value('@trailerNo[1]','VARCHAR(20)') AS [trailerNo],
        E.n.value('@year[1]','VARCHAR(20)') AS [year],
        E.n.value('@day[1]','VARCHAR(20)') AS [day],
        E.n.value('@month[1]','VARCHAR(20)') AS [month],
        E.n.value('@hour[1]','VARCHAR(20)') AS [hour],
        E.n.value('@min[1]','VARCHAR(20)') AS [min],
        E.n.value('@local[1]','VARCHAR(20)') AS [local],
        E.n.value('@timeZone[1]','VARCHAR(20)') AS [timeZone],
        I.n.value('@InfoName[1]','VARCHAR(20)') AS [InfoName],
        I.n.value('@infoId[1]','VARCHAR(20)') AS [infoId]       
    FROM 
    	@x.nodes('GoldFields') A(n)
    	OUTER APPLY
    	A.n.nodes('OutboundLoadInformation') AS B(n)
    	OUTER APPLY
    	B.n.nodes('OBDetail') AS C(n)
    	OUTER APPLY
    	C.n.nodes('OBAction') AS D(n)
    	OUTER APPLY
    	D.n.nodes('OBDate') AS E(n)
    	OUTER APPLY
    	C.n.nodes('OBInfoLow') AS I(n);
    GO
    /*
    
    OrderNumber	trailerNo	year	day	month	hour	min	local	timeZone	InfoName	infoId
    02-0383		2013	26	02	14	00	-2	MST	OUTBOUND	
    02-0383		2013	28	02	14	00	-1	CST	NULL	NULL
    
    */


    AMB

    Some guidelines for posting questions...

    lunes, 11 de marzo de 2013 18:22
  • Gracias Hunchback,

    Saludos

    cordiales,

    lunes, 11 de marzo de 2013 22:33