Procedimiento que hace una select desde un XML en SQL-Server

Bueno, estoy realizando un cursillo de SQL-Server2008 R2 cortesía de la Xunta y la UE (hay que aprovechar, que a ver lo que dura la formación gratis en este país). Así que vamos con pequeño ejemplo: un procedmiento que realiza un select sobre un fichero xml, que recibe como parámetro.

Primero vamos a definir una variable como xml (podríamos también importarlo usando un bulk import, pero así veis más clara):

declare @EjemploXML as xml

set @EjemploXML=
'<?xml version="1.0"?>
<SalespersonMods>
    <SalespersonMod SalespersonID="274">
        <Mods>
            <Mod SalesTerritoryID="3"/>
        </Mods>
    </SalespersonMod>
    <SalespersonMod SalespersonID="278">
        <Mods>
            <Mod SalesTerritoryID="4"/>
        </Mods>	
    </SalespersonMod>
<SalespersonMods>'

En fin, ahí tenéis el xml que vamos a recorrer. Como véis la «chicha» está en los atributos SalespersonID y SalesTerritoryID, que son los datos que queremos sacar en la consulta. Ahora os dejo el código y debajo explico todo el procedimiento:


create procedure selectFromXML
@SalesPersonMods xml
as
begin
  declare @XMLdoc int
  exec sp_xml_preparedocument @XMLdoc output, @SalesPersonMods
  select * from
  openxml(@XMLdoc, '/SalespersonMods/SalespersonMod/Mods/Mod')
  with
  (
	SalesTerritoryID int '@SalesTerritoryID',
	SalespersonId int '../../@SalespersonID'
  )

  exec sp_xml_removedocument @XMLdoc
end

En fin, y explicado rápido: Lo primero es crear el procedimiento con create procedure seguido del nombre que queremos darle, y especificando que recibirá como parámetro @SalesPersonMods, que será un xml. Tras eso declaramos un variable entera (la he llamado @XMLdoc) que almacenará un manejador. Para crear dicho manejador usamos el procedimiento almacenado de sistema sp_xml_preparedocument, que recibe como parámetros la variable entera @XMLdoc (definido como output) y la variable xml @SalesPersonMods. Ya tenemos el manejador listo. Ahora queda hacer la select, que empieza como cualquier select normal: select ‘loquesea’ from (en este caso *, o sea, todo). Y en este caso en vez del nombre de tabla irá la función openxml, que recibirá como parámetros el manejador que creamos antes (@XMLdoc) y una cadena de texto con el elemento del xml en el que queremos posicionarnos.

Movernos por el xml es como movernos por MS-DOS, usamos la jerarquía de etiquetas separadas por barras (/) como si fuera una jerarquía de directorios, usando el punto (.) para referirnos a elementos que parten del mismo nivel jerárquico y el doble punto (..) para navegar hacia atrás en la jerarquía. En este caso queremos acceder al atributo SalesTerritoryID, que es un atributo de /Mod (donde nos hemos situado), por lo que no hay que navegar, accedemos a él simplemente con @SalesTerritoryID (a los atributos nos referimos siempre precediéndolos de una arroba, si fuera texto en la etiqueta lo haríamos sin ella) dándole como nombre de columna SalesTerritoryID (definida como entero, en este caso, por ser el valor más adecuado). SalespersonID en cambio está dos niveles más arriba, así que llegamos a él indicándolo con el doble punto: «../../SalespersonID».

Finalmente, tras todas las operaciones, quitamos el manejador de la memoria para liberarla. Si quieres probar la funcionalidad de esto te basta con copiar y todo el código en el QueryManager de SQL-Server. Finalmente ejecuta el procedimiento pasándole como parámetro la variable sql que definimos arriba y comprueba el resultado.

Paginación con PHP+MySQL

Hoy me encontraba con la necesidad de hacer uso de la paginación en un desarrollo PHP. Nunca lo había hecho previamente en este lenguaje, aunque sí había tenido que hacerlo en VB.NET (tablas paginadas a gogo en mi paso por Nática SL) y en Java. Entre la experiencia con otros lenguajes y las búsquedas en San Google he encontrado como hacerlo.

Para esto vamos a utilizar dos funciones de MySQL: SQL_CALC_FOUND_ROWS, que nos permite calcular el número de resultados de una consulta sin LIMIT, y FOUND_ROWS, que nos permite recuperar el resultado de la última ejecución de la función anterior. Estos dos métodos nos ahorrarán hacer una consulta COUNT para saber el total de resultados, que necesitaremos para calcular la cantidad de páginas que se mostrarán.

Supongo que ya sabéis crear una conexión con una base de datos MySQL en PHP, así que es lo primero que debéis hacer.

Lo siguiente es definir una variable con el número máximo de resultados a mostrar por página:

 $max = 10

En mi caso la fijé en 10 resultados. Luego hay que definir qué página se mostrará, que en principio se pedirá usando el método GET:

$pagina = (int) $_GET[‘pag’];

if($pagina<1)

{

$pagina = 1;

}

$offset = ($pagina-1)*$max;

Luego viene la creación de la consulta paginada y de la consulta para conocer el total.

$sql = «SELECT SQL_CALC_FOUND_ROWS id, name FROM clientes LIMIT $offset, $max»;

$sqlTotal = «SELECT FOUND_ROWS() as total»;

Tras esto, recogemos los Result Set de ambas consultas, con los que podemos calcular el total y almacenar los resultados.

$resultSet = mysql_query($sql);

$rsTotal = mysql_query($sqlTotal);

$filasTotal = mysql_fetch_assoc($rsTotal);

$total = $filasTotal[‘total’];

Bueno, ahora sólo nos queda crear una tabla en la que mostrar los resultados, con los enlaces a las distintas páginas debajo.

border=»1″ bordercolor=»#0000EE»>
<thead>
<tr>
<td>Id</td>
<td>Name</td>
</tr>
</thead>
<tbody>
<?php
while ($fila = mysql_fetch_assoc($resultSet))
{
$id = $fila[«id»];
$name = htmlentities($fila[«nombre»]);
?>
<tr>
<td><?php echo $id; ?></td>
<!–?php echo $name; ?>–>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<tr>
colspan=»2″>
<?php
$totalPag = ceil($total/$max);
$enlaces = array();
for( $i=1; $i<=$totalPag ; $i++)
{
$enlaces[] = «href=\»?pag=$i\»>$i«;
}
echo implode(» – «, $links);
?>
</td>
</tr>
</tfoot>
</table>

Tenéis que perdonar el deficiente sangrado del código, pero al ponerlo como comentario siempre queda descojonado. En cualquier caso podéis probar a copiar y pegar en un editor como Aptana Studio, donde el sangrado automático arreglaría la cuestión.

En fin, espero que esto os sea de ayuda.