Ayuda con Lectura de XML con Namespace

Question

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,

Answer 1

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...

Answer 2

Gracias Hunchback,

Saludos

cordiales,