Recientemente en un proyecto nos hemos encontrado con la necesidad de leer un parametro XML desde una función T-SQL y convertirla en una tabla para poder realizar un JOIN con los valores de otra tabla de la base de datos. En este caso no nos sirven las nuevas Table-Value de SQL Server 2008, ya que el resultado proviene de un campo XML y el servidor es un 2005, así que tuvimos que explorar las distintas posibilidades de trabajo XML de SQL Server 2005.
Anteriormente, en algunas ocasiones había usado parámetros XML para enviar varios valores a un stored procedure o a una función, pero siempre con un sólo campo (el clásico ejemplo de cómo pasar varios IDs a un SP para hacer un JOIN y filtrar valores de una tabla).
Leer valores simples de un parámetro XML
Partiendo de un valor XML como este:
<Usuarios>
<ID>S-1-5-21-823518204-1220945662-725345543-1138</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-1133</ID>
<ID>S-1-1-0</ID>
<ID>S-1-5-32-545</ID>
<ID>S-1-5-32-544</ID>
<ID>S-1-5-4</ID>
<ID>S-1-5-11</ID>
<ID>S-1-2-0</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-1231</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-512</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-1123</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-1219</ID>
<ID>S-1-5-21-823518204-1220945662-725345543-513</ID>
</Usuarios>
Deseamos enviarlo a un SP y hacer un JOIN con los datos de una tabla de usuarios:
| UserSID |
UserName |
UserType |
| S-1-5-21-823518204-1220945662-725345543-1138 |
OLIMPUS\Zeus |
A |
| S-1-5-21-823518204-1220945662-725345543-1137 |
OLIMPUS\Apolo |
B |
| S-1-5-21-1960408961-1788223648-839522115-500 |
OLIMPUS\Guest |
NULL |
| S-1-5-32-544 |
USERMACHINE\Administrator |
A |
Para ello en un SP o función declaramos el parámetro (@IDUsuarios As XML) y una tabla temporal en la que almacenar los datos de XML:
DECLARE @Usuarios TABLE (ID varchar(50))
INSERT INTO @Usuarios (ID)
SELECT ParamValues.ID.value('.','VARCHAR(50)')FROM @IdUsuarios.nodes('/Usuarios/ID') as ParamValues(ID)
Y a continuación realizamos el JOIN entre las dos tablas. Simple, sencillo y elegante.
SELECT U.* FROM Users U
INNER JOIN @Usuarios UIDS
ON UIDS.ID = U.UserSID
Leer múltiples campos de un parámetro XML
Pero... ¿Y si el parámetro XML contiene más de un valor o campo? Supongamos un XML como el siguiente:
<Orders>
<Order OrderID="13000" CustomerID="ALFKI" OrderDate="2006-09-20Z" EmployeeID="2"></Order>
<Order OrderID="13001" CustomerID="VINET" OrderDate="2006-09-20Z" EmployeeID="1"></Order>
</Orders>
En este caso la dificultad esta en que el XML contiene más de un campo, con lo que el uso de la sintaxis ParamValues.ID.value no es válida. De modo que vamos a usar la sintaxis @Parametro.nodes para devolver cada elemento 'Orders' como una fila y Item.value para acceder a cada uno de los campos:
SELECT OrderID = T.Item.value('@OrderID', 'int'), CustomerID = T.Item.value('@CustomerID', 'nchar(5)'), OrderDate = T.Item.value('@OrderDate', 'datetime'), EmployeeId = T.Item.value('@EmployeeID', 'smallint')FROM @x.nodes('Orders/Order') AS T(Item)
Bien, bien, bien... Ya hemos visto como transformar un xml en una tabla, ahora que más?
Leer subnodos de un parámetro XML
<Orders>
<Order OrderID="13000" CustomerID="ALFKI" OrderDate="2006-09-20Z" EmployeeID="2">
<OrderDetails ProductID="76" Price="123" Qty = "10"/>
<OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
</Order>
<Order OrderID="13001" CustomerID="VINET" OrderDate="2006-09-20Z" EmployeeID="1">
<OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
</Order>
</Orders>
En este caso tenemos un documento XML más complejo, con una jerarquía de elementos 'Order' que a su vez contienen elementos 'OrderDetails'. Ok, si queremos acceder a todos los elementos 'OrderDetail' del XML basta con cambiar la consulta XPath y el tipo de los elementos devueltos:
SELECT OrderID = T.Item.value('../@OrderID', 'int'), ProductID = T.Item.value('@ProductID', 'smallint'), Price = T.Item.value('@Price', 'decimal(10,2)'), Qty = T.Item.value('@Qty', 'int')FROM @x.nodes('Orders/Order/OrderDetails') AS T(Item)
Y si sólamente deseamos obtener algunos de ellos, podemos hacer uso de la cláusula WHERE de este modo:
SELECT OrderID = T.Item.value('../@OrderID', 'int'), ProductID = T.Item.value('@ProductID', 'smallint'), Price = T.Item.value('@Price', 'decimal(10,2)'), Qty = T.Item.value('@Qty', 'int')FROM @x.nodes('Orders/Order/OrderDetails') AS T(Item)WHERE T.Item.value('../@OrderID', 'int') = 13000
Sencillo, verdad? Pues ahora a probarlo.
Demo
Puedes pegar este código en el analizador de consultas y pruébalo:
DECLARE @x xml
SELECT @x =
N'<Orders>
<Order OrderID="13000" CustomerID="ALFKI" OrderDate="2006-09-20Z" EmployeeID="2">
<OrderDetails ProductID="76" Price="123" Qty = "10"/>
<OrderDetails ProductID="16" Price="3.23" Qty = "20"/>
</Order>
<Order OrderID="13001" CustomerID="VINET" OrderDate="2006-09-20Z" EmployeeID="1">
<OrderDetails ProductID="12" Price="12.23" Qty = "1"/>
</Order>
</Orders>'
SELECT OrderID = T.Item.value('@OrderID', 'int'), CustomerID = T.Item.value('@CustomerID', 'nchar(5)'), OrderDate = T.Item.value('@OrderDate', 'datetime'), EmployeeId = T.Item.value('@EmployeeID', 'smallint')FROM @x.nodes('Orders/Order') AS T(Item)
SELECT OrderID = T.Item.value('../@OrderID', 'int'), ProductID = T.Item.value('@ProductID', 'smallint'), Price = T.Item.value('@Price', 'decimal(10,2)'), Qty = T.Item.value('@Qty', 'int')FROM @x.nodes('Orders/Order/OrderDetails') AS T(Item)
Por cierto, ya sabeis por que en XML siempre es necesario cerrar las etiquetas verdad? Porque si no en un caso como el siguiente el efecto visual sería bastante feo...
Ya se que este no es lugar para estas cosas, pero no he podido resistirme... :-P
Un saludo desde Andorra,