Datos incompletos en lectura XML

Question

hola amigos de la comunidad, estoy leyendo archios XML y pasandolo a lineas SQL mediante sp_xml_preparedocument, tengo el siguiente problema, mi query que muestro sólo me muestra una fila cuando debe de mostrarse dos, al parecer es porque no están todos los hijos en la fila 1, yo pienso que si no están los hijos deben salir nulos o vacios pero SQL no me los muestra hasta que los agregue, mi escenario es que me pueden llegar XML de este tipo, por favor alguna idea para superar este incoveniente,

declare @DocumentoXML nvarchar(max);
set @DocumentoXML = '
<SantyConfirmMessage shipperReferenceNumber="259870" >
  <NodeDetail seqInRoute="0" Code="AO-SAN0-70103" Destiny="Santy LLC-Amarillo Gold">
    <NodeAction actionType="F"/>
  </NodeDetail>
  <NodeDetail seqInRoute="1" Code="AO-SAN0-00048" Destiny="GROBB CABLE INC.">
    <NodeAction actionType="F">
      <NodeActionDate Status="confirmed" year="2013" day="15" month="1" hour="8" min="30"/>
    </NodeAction>
  </NodeDetail>
</SantyConfirmMessage>
';

declare @IdDoc int;

EXEC sp_xml_preparedocument @IdDoc OUTPUT, @DocumentoXML

		SELECT * 
		FROM  OPENXML (@IdDoc, '/SantyConfirmMessage/NodeDetail/NodeAction/NodeActionDate',1)
		        WITH (
		              [shipperReferenceNumber]  varchar(20) '../../../@shipperReferenceNumber',
		              [seqInRoute]  varchar(10) '../../@seqInRoute',
					  [Code] varchar(20) '../../@Code',
					  [Destiny] varchar(30) '../../@Destiny',
					  [actionType] varchar(20) '../@actionType',
					  [Status] varchar(20) '@Status',
					  [year] varchar(20) '@year',
					  [day] varchar(20) '@day',
					  [month] varchar(20) '@month',
					  [hour] varchar(20) '@hour',
					  [min]  varchar(20) '@min'
					  );

EXEC sp_xml_removedocument @IdDoc;

Saludos,

Answer 1

Hola.

Te posicionas en el punto incorrecto, si quieres obtener todos los "NodeAction", has de colocarte ahí, no en el nodo interior, que como sólo hay uno, impide que se obtenga el otro registro. Prueba así:

declare @DocumentoXML nvarchar(max);
set @DocumentoXML = '
<SantyConfirmMessage shipperReferenceNumber="259870" >
  <NodeDetail seqInRoute="0" Code="AO-SAN0-70103" Destiny="Santy LLC-Amarillo Gold">
    <NodeAction actionType="F"/>
  </NodeDetail>
  <NodeDetail seqInRoute="1" Code="AO-SAN0-00048" Destiny="GROBB CABLE INC.">
    <NodeAction actionType="F">
      <NodeActionDate Status="confirmed" year="2013" day="15" month="1" hour="8" min="30"/>
    </NodeAction>
  </NodeDetail>
</SantyConfirmMessage>
';

declare @IdDoc int;

EXEC sp_xml_preparedocument @IdDoc OUTPUT, @DocumentoXML

		SELECT * 
		FROM  OPENXML (@IdDoc, '/SantyConfirmMessage/NodeDetail/NodeAction',1)
		        WITH (
		              [shipperReferenceNumber]  varchar(20) '../../@shipperReferenceNumber',
		              [seqInRoute]  varchar(10) '../@seqInRoute',
					  [Code] varchar(20) '../@Code',
					  [Destiny] varchar(30) '../@Destiny',
					  [actionType] varchar(20) '@actionType',
					  [Status] varchar(20) 'NodeActionDate/@Status',
					  [year] varchar(20) 'NodeActionDate/@year',
					  [day] varchar(20) 'NodeActionDate/@day',
					  [month] varchar(20) 'NodeActionDate/@month',
					  [hour] varchar(20) 'NodeActionDate/@hour',
					  [min]  varchar(20) 'NodeActionDate/@min'
					  );

EXEC sp_xml_removedocument @IdDoc;

---

Alberto López Grande
SQL Server MVP
Visita mi blog en http://qwalgrande.com
Sígueme en twitter en http://twitter.com/qwalgrande

Answer 2

Lo que pasa es que solo tienes un unico element [NodeActionDate] y por lo tanto no tendras acceso a los otros elementos que no estan conectados a este. Te recomiendo que busques por [NodeAction] y que desde este referencies hijos y padres.

USE tempdb;
GO
declare @DocumentoXML nvarchar(max);
set @DocumentoXML = '
<SantyConfirmMessage shipperReferenceNumber="259870" >
  <NodeDetail seqInRoute="0" Code="AO-SAN0-70103" Destiny="Santy LLC-Amarillo Gold">
    <NodeAction actionType="F"/>
  </NodeDetail>
  <NodeDetail seqInRoute="1" Code="AO-SAN0-00048" Destiny="GROBB CABLE INC.">
    <NodeAction actionType="F">
      <NodeActionDate Status="confirmed" year="2013" day="15" month="1" hour="8" min="30"/>
    </NodeAction>
  </NodeDetail>
</SantyConfirmMessage>
';

declare @IdDoc int;

EXEC sp_xml_preparedocument @IdDoc OUTPUT, @DocumentoXML

		SELECT * 
		FROM  OPENXML (@IdDoc, '/SantyConfirmMessage/NodeDetail/NodeAction', 1)
		        WITH (
		              [shipperReferenceNumber]  varchar(20) '../../@shipperReferenceNumber',
		              [seqInRoute]  varchar(10) '../@seqInRoute',
					  [Code] varchar(20) '../@Code',
					  [Destiny] varchar(30) '../@Destiny',
					  [actionType] varchar(20) '@actionType',
					  [Status] varchar(20) 'NodeActionDate/@Status',
					  [year] varchar(20) 'NodeActionDate/@year',
					  [day] varchar(20) 'NodeActionDate/@day',
					  [month] varchar(20) 'NodeActionDate/@month',
					  [hour] varchar(20) 'NodeActionDate/@hour',
					  [min]  varchar(20) 'NodeActionDate/@min'
					  );

EXEC sp_xml_removedocument @IdDoc;
GO
/*

shipperReferenceNumber	seqInRoute	Code	Destiny	actionType	Status	year	day	month	hour	min
259870	0	AO-SAN0-70103	Santy LLC-Amarillo Gold	F	NULL	NULL	NULL	NULL	NULL	NULL
259870	1	AO-SAN0-00048	GROBB CABLE INC.	F	confirmed	2013	15	1	8	30

 con el sus */

Si estas usando nvarchar(max) entonces infiero que usas SS 2005 o mayor, y por lo tanto te recomiendo que uses el tipo de dato XML y con el sus metodos.

USE tempdb;
GO
DECLARE @x xml = '<SantyConfirmMessage shipperReferenceNumber="259870" >
  <NodeDetail seqInRoute="0" Code="AO-SAN0-70103" Destiny="Santy LLC-Amarillo Gold">
    <NodeAction actionType="F"/>
  </NodeDetail>
  <NodeDetail seqInRoute="1" Code="AO-SAN0-00048" Destiny="GROBB CABLE INC.">
    <NodeAction actionType="F">
      <NodeActionDate Status="confirmed" year="2013" day="15" month="1" hour="8" min="30"/>
    </NodeAction>
  </NodeDetail>
</SantyConfirmMessage>';

SELECT
	A.n.value('@shipperReferenceNumber[1]', 'varchar(20)') AS shipperReferenceNumber,
	B.n.value('@seqInRoute[1]', 'varchar(10)') AS seqInRoute, 
	B.n.value('@Code[1]', 'varchar(20)') AS Code,
	B.n.value('@Destiny[1]', 'varchar(30)') AS Destiny,
	C.n.value('@actionType[1]', 'varchar(20)') AS actionType,
	D.n.value('@Status[1]', 'varchar(20)') AS [Status],
	D.n.value('@year[1]', 'varchar(20)') AS [year],
	D.n.value('@day[1]', 'varchar(20)') AS [day],
	D.n.value('@month[1]', 'varchar(20)') AS [month],
	D.n.value('@hour[1]', 'varchar(20)') AS [hour],
	D.n.value('@min[1]', 'varchar(20)') AS [min]
FROM
	@x.nodes('/SantyConfirmMessage') AS A(n)
	OUTER APPLY
	A.n.nodes('NodeDetail') AS B(n)
	OUTER APPLY
	B.n.nodes('NodeAction') AS C(n)
	OUTER APPLY
	C.n.nodes('NodeActionDate') AS D(n);
GO

No solo te deshaces de los procedimientos encargados de preparar y remover el documento XML, sino que no tienes por que saber si existen elementos [NodeActionDate] o no para poder accesar a sus padres.

---

AMB

Some guidelines for posting questions...

Answer 3

Gracias amigos, funciono los dos métodos, me parece interesante lo que propones Hunchback, pero mi escenario es que mis sistemas envían el XML en una cadena de tipo String, alguna idea para convertir esa cadena String a XML?

Saludos

Gracias,

Answer 4

Gracias, al pasar por la documentación de SQL encontre que es un tipo de conversion implicita.

Saludos,