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.