Requerimiento: Necesitamos consultar una base de datos de recursos humanos donde la data de los postulantes ha sido almacenada en formato XML. Debemos usar las funciones nativas de SQL Server para obtener dicha información.
Solución: El formato XML, además de brindarnos un modelo independiente que nos permite la portabilidad de nuestros datos a otras plataformas, es muy útil cuando queremos almacenar data no estructurada.
En este ejemplo crearemos una tabla dbo.Candidato que almacenará el curriculum vitae de los postulantes en formato XML e insertaremos 4 registros de prueba.
use [BitacoraDBA]
CREATE TABLE dbo.Candidato
(id int,
cv XML);
GO
INSERT INTO dbo.Candidato (id, cv)
VALUES
(1, N' <curriculum_vitae>
<Nombre_Completo>
<Nombre>Shai</Nombre>
<Apellido>Bassli</Apellido>
</Nombre_Completo>
<Aptitudes>I am an experienced and versatile machinist who can operate a range of machinery personally as well as supervise the work of other machinists.
</Aptitudes>
</curriculum_vitae>'),
(2, N' <curriculum_vitae>
<Nombre_Completo>
<Nombre>Max</Nombre>
<Apellido>Benson</Apellido>
</Nombre_Completo>
<Aptitudes>3 years recent experience as a go-cart production line manager. Responsibilities included planning the production line budget, ordering parts, and overseeing all quality assurance procedures.
</Aptitudes>
</curriculum_vitae>'),
(3, N' <curriculum_vitae>
<Nombre_Completo>
<Nombre>Krishna</Nombre>
<Apellido>Sunkammurali</Apellido>
</Nombre_Completo>
<Aptitudes>Expert in C# and Visual Basic 6.0. 7 years experience in object-oriented programming. Familiar with ASP.Net and the .NET Framework. Design experience with both Windows and Web user interfaces.
</Aptitudes>
</curriculum_vitae>'),
(4, N' <curriculum_vitae>
<Nombre_Completo>
<Nombre>Kyle</Nombre>
<Apellido>Penuchot</Apellido>
</Nombre_Completo>
<Aptitudes>Mécanicien expérimenté et polyvalent qui peut utiliser diverses machines ou superviser le travail d''autres mécaniciens. Je suis spécialisé dans les diagnostics et l''inspection de précision. Je sais lire des plans et peux faire appel à mes compétences en matière de communication pour guider le travail d''autres mécaniciens de production dont je suis amené à inspecter le travail.
</Aptitudes>
</curriculum_vitae>');
Como acabamos de ver, hemos creado una columna XML llamada ‘cv’ que guarda nombres, apellidos y aptitudes de cada postulante. Un registro de este tipo se vería así:
<curriculum_vitae>
<Nombre_Completo>
<Nombre>Shai</Nombre>
<Apellido>Bassli</Apellido>
</Nombre_Completo>
<Aptitudes>I am an experienced and versatile machinist who can operate a range of machinery personally as well as supervise the work of other machinists.
</Aptitudes>
</curriculum_vitae>
XQuery es el lenguaje usado para consultar data almacenada en formato XML, por lo que si queremos, por ejemplo, obtener todos los candidatos cuyo nombre comience con la letra K ejecutaremos lo siguiente:
SELECT * FROM Candidato
WHERE cv.value('(/curriculum_vitae/Nombre_Completo/Nombre)[1]','nvarchar(max)') LIKE 'K%'
El método value usado en el ejemplo anterior nos permite extraer el valor de un campo almacenado en formato XML, y la hemos utilizado para poder obtener todos los datos del candidato que hagan match con la condición dada. Puedes leer más acerca de este método aquí.
Métodos value y exist
Si queremos obtener sólo las aptitudes de los candidatos en cuyo nombre figure la letra ‘K’ nuestra query sería así:
SELECT cv.value('(/curriculum_vitae/Aptitudes)[1]','nvarchar(max)') FROM dbo.Candidato
WHERE cv.exist('(/curriculum_vitae/Nombre_Completo/Nombre[contains(.,"K")])') =1
Además de usar el método value, aquí hemos usado el método exist, que nos devuelve un valor bit 1 o 0 dependiendo de si la condición dada es verdadera o falsa. En el ejemplo anterior la condición es que el campo nombre contenga la letra K. Puedes leer más acerca de este método aquí.
Para aplicar el cambio, la máquina virtual debe estar apagada.
Conclusiones
El formato XML es muy útil para almacenar data no estructurada o semi-estructurada. Junto con ello podemos listar una serie de ventajas que conlleva el uso de este tipo de dato frente a otros en ciertos escenarios.
El lenguaje XQuery es el lenguaje usado para realizar consultas de datos XML y posee varios métodos para dicho propósito, entre ellos .value y .exist. Conoces algún otro método?
Si tienes alguna duda, comentarios o sugerencias, me puedes enviar un correo a bitácoradeundba@gmail.com.