Hola Marianito,
El query que te pase es muy parecido a uno que esta en una pagina linkeada
en el texto de la ayuda que vos me pasaste [1].
Example: Search Context Defined by XML Data Type Methods
An alternative to the exist() method is to use the LIKE operator on the text
value of the context node as shown in the following code. This yields a
case-insensitive, substring search.
SELECT pk, xCol
FROM docs CROSS APPLY xCol.nodes('/book/title/text()') title(tRef)
WHERE CONTAINS (xCol, 'Secure')
AND title.tRef.value ('.', 'NVARCHAR(MAX)') LIKE '%Secure%'
La tabla es de una aplicacion mia, la puedo tocar sin problemas. No me sirve
la opcion del trigger porque el contenido del campo XML no es uniforme, no
sabria que tengo que guardar en esta columna "aplanada". Ademas, si me
cambia el criterio de busqueda (buscar por otro campo) tendria que correr de
nuevo todos los triggers. Ademas, no me gustan los triggers :-)
Por el mismo motivo tampoco me gusta/sirve la opcion 2.
Tengo que suar contains porque necesito full text search sobre el campo XML
y, en algunas busquedas, hacer el contains sobre un nodo especifico.
Entonces, con el contains basado en FTI filtro lo grueso, obteniendo los
registros que tienen en cualquier nodo el texto buscado. Con el segundo
contains (el de XQuery) refino este resultset mas grande a los registros que
tienen el texto buscado en algun lugar de UN NODO en particular.
Esto funciona perfectoamente y es muy rapido (gracias al FTI, no a los
indices XML, me imagino).
El unico detalle es que el contains de XQuery es case sensitive, de ahi que
quiera usar el LIKE.
Gracias!
[1] XML Support in Microsoft SQL Server 2005,
http://msdn.microsoft.com/en-us/library/ms345117.aspx
Carlos Peix
_____
De: [email protected] [mailto:[email protected]] En nombre de Jose Mariano
Alvarez
Enviado el: Jueves, 08 de Enero de 2009 01:01 p.m.
Para: [email protected]
Asunto: [dbms] Query full text search y XML
No creo que aunque te funcione rl CROSS APPLY funcione mas rápido ya que
creo tiene que resolver primero DatosXML.nodes('/Persona/Nombre/text()')
nombre(tRef). para convertirlo en una tabla y menos con un LIKE '%jose%'
Me pasas el link de donde sacaste esa idea?
Dos alternativas:
1 - Pone un trigger (INS,UPD) donde sacas el value del path dentro del XML y
ponelo en una columna de la tabla, luego indexala con FTS
2- Crea una columna calculada con el .path dentro del XML y luego indexala
con FTS. (esto no se si lo tolera)
Los indices XML deberían alcanzar pero si usas contains en el XQUERY casi
seguro va a hacer un scan del indice XML por lo que seria necesario usar
alguna de las sugerencias anteriores...
Deberías ver los planes de ejecución.
Por que tenes que usar contains.
La tabla es diseño tuyo y se puede modificar?
Si no se puede la unica sugerencia posible es la 1 con el agregado de una
tabla de busqueda (PK y resultado del PATH) y el trigger por INS, UPD, y DEL
en la tabla original.
--
--------------------------------
Ing. José Mariano Alvarez
SQL Total Consulting
Bogota 3631 P3B
1407 Buenos Aires-Argentina
Movil: (011)-15-4184-7541
Desde el exterior: (+54-911)-4184-7541
[email protected]
2009/1/7 Carlos Peix <[email protected]>
Hola Mariano,
Ya recorri este camino, al final del articulo que me pegaste aqui refiere a
un articulo (http://msdn.microsoft.com/en-us/library/ms345117.aspx) el cual
estuve leyendo tambien y, luego de recorrer varios articulos mas, llegue al
query que postee y que parece adecuado, solo que no funciona.
Ya tengo en al columna un indice XML primario y los tres secundarios, ademas
del FTS.
Tomando en cuenta que la tabla puede tener un millon de registros, vos crees
que me alcanzara con los indices XML sin usar FTS o no?
Yo entiendo que necesito la ayuda de un FTS, pero no estoy seguro.
Carlos Peix
_____
De: [email protected] [mailto:[email protected]] En nombre de Jose Mariano
Alvarez
Enviado el: Miércoles, 07 de Enero de 2009 05:27 p.m.
Para: [email protected]
Asunto: [dbms] Query full text search y XML
Un esquema asociado a una columna XML es basicamente un constraint. Si no
tenes un esquema no va a poder eoptimizar el almacenamiento ahorrando
espacio y mejorando los accesos.
Sugiero que crees un indice por la columna XML y luego uno secundario
seguramente por valor..
Luego hace la búsqueda
XML Indexes in SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms345121.aspx
Te copio del manual
[PRIMARY] XML
Crea un índice XML en la columna xml especificada. Cuando se especifica
PRIMARY, se crea un índice agrupado con la clave agrupada formada a partir
de la clave de agrupación de la tabla de usuario y un identificador del nodo
XML. Cada tabla puede tener hasta 249 índices XML. Observe lo siguiente
cuando cree un índice XML:
* Debe existir un índice agrupado en la clave principal de la tabla de
usuario.
* La clave de agrupación de la tabla de usuario tiene un límite de 15
columnas.
* Cada columna xml de una tabla puede tener un índice XML principal y
varios índices XML secundarios.
* Debe existir un índice XML principal en una columna xml para poder
crear un índice XML secundario en la columna.
* Sólo se puede crear un índice XML en una columna xml única. No puede
crear un índice XML en una columna que no sea xml, como tampoco puede crear
un índice relacional en una columna xml.
* No puede crear un índice XML, ya sea principal o secundario, en una
columna xml en una vista, en una variable con valores de tabla con columnas
xml o en variables de tipo xml.
* No puede crear un índice XML principal en una columna xml calculada.
* La configuración de la opción SET debe ser la misma que la requerida
para vistas indizadas e índices de columnas calculadas. Específicamente, se
debe establecer la opción ARITHABORT en ON cuando se crea un índice XML y
cuando se insertan, eliminan o actualizan valores en la columna xml. Para
obtener más información, vea Opciones SET que afectan a los resultados.
Para obtener más información, vea Índices en columnas de tipo de datos xml.
xml_column_name
Es la columna xml en la que se basa el índice. Sólo se puede especificar una
columna xml en una única definición de índice XML; sin embargo, se pueden
crear varios índices XML secundarios en una columna xml.
USING XML INDEX xml_index_name
Especifica el índice XML principal que se utilizará en la creación de un
índice XML secundario.
FOR { VALUE | PATH | PROPERTY }
Especifica el tipo de índice XML secundario.
VALUE
Crea un índice XML secundario en las columnas en las que se encuentran las
columnas de clave (ruta y valor del nodo) del índice XML principal.
PATH
Crea un índice XML secundario en las columnas generadas a partir de valores
de ruta y de nodo del índice XML principal. En el índice secundario PATH,
los valores de ruta y de nodo son las columnas de clave que permiten
exploraciones eficaces en la búsqueda de rutas.
PROPERTY
Crea un índice XML secundario en las columnas (PK, y valor de ruta y de
nodo) del índice XML principal, donde PK es la clave principal de la tabla
base.
Mas info
Use full-text search in conjunction with XQuery: To search XML documents or
do content-sensitive queries on text, you can combine SQL Server full-text
search with XQuery. Full-text search will index the text nodes of XML
documents, though not the elements or attributes. You can use the FULLTEXT
contains verb to do stem-based or context-based queries over an entire
collection of documents (this is most often the top-down part of the query)
to select individual documents to operate on, then use XQuery to do
structural element and attribute-sensitive structures. Remember that the
XQuery contains verb is not at all the same as the FULLTEXT contains verb.
XQuery contains is a substring-based function and the SQL Server 2005
implementation uses a binary collation. See XML
<http://msdn.microsoft.com/en-us/library/cc411401.aspx> Support in Microsoft
SQL Server 2005 by Shankar Pal et al., for an example of combining fulltext
and XQuery.
--
--------------------------------
Ing. José Mariano Alvarez
SQL Total Consulting
Bogota 3631 P3B
1407 Buenos Aires-Argentina
Movil: (011)-15-4184-7541
Desde el exterior: (+54-911)-4184-7541
[email protected]
On Wed, Jan 7, 2009 at 16:50, Carlos Peix <[email protected]> wrote:
Hola Mariano,
La estructura de los documentos XML es variable, corresponde a distintos
tipos de objetos serializadoes mediante serializacion XML del framework. Por
lo tanto, si no te entiendo mal, la respuesta es no, los XML no tienen un
esquema definido.
Pero, en ciertos casos particulares como el ejemplo que indique, puedo
asegurar que el documento XML tiene una estructura predefinida.
No necesito, en estos casos, usa contains? cual de los dos contains es el
que te preocupa?
Gracias
Carlos Peix
_____
De: [email protected] [mailto:[email protected]] En nombre de Jose Mariano
Alvarez
Enviado el: Miércoles, 07 de Enero de 2009 04:25 p.m.
Para: [email protected]
Asunto: [dbms] Query full text search y XML
Por que contains?
El XML tiene algun schema que cumple?
--
--------------------------------
Ing. José Mariano Alvarez
SQL Total Consulting
Bogota 3631 P3B
1407 Buenos Aires-Argentina
Movil: (011)-15-4184-7541
Desde el exterior: (+54-911)-4184-7541
[email protected]
2009/1/7 Carlos Peix <[email protected]>
Hola gente,
Tengo una tabla con una columna de tipo XML, mas o menos asi (pseudo DDL):
CREATE TABLE DatosXML
id int,
datos xml
Tambien tengo un full text index sobre la columna datos.
A partir de esto tengo posibilidad de hacer el siguiente query:
SELECT * FROM DatosXML
WHERE CONTAINS(datos, 'Jose')
Eso funciona bien pero me busca en todo el XML.
El siguiente paso es extender la busqueda para encontrar registros que
tengan un determinado string en un determinado nodo del XML. Llegue a esta
consulta:
SELECT * FROM DatosXML
WHERE CONTAINS(datos, 'Jose') AND
datos.exist('/Persona/Nombre/text()[fn:contains(.,"Jose")]') = 1
La idea es que el contains sobre el FTI filtre lo mas grueso de la consulta
y luego, el fn:contains(), filtre mas especificamente.
Eso tambien funciona, salvo que fn:contains() es case sensitive, por lo que
no puedo usarlo realmente como un full text search.
Googleando un poco mas encontre esta idea (siempre sobre el mismo DDL):
SELECT *
FROM DatosXML CROSS APPLY DatosXML.nodes('/Persona/Nombre/text()')
nombre(tRef)
WHERE CONTAINS(datos, 'Jose') AND
nombre.tRef.value ('.', 'NVARCHAR(MAX)') LIKE '%jose%'
La idea, segun entiendo, es sacar el nodo XML fuera y compararlo con un
like, con lo cual tengo disponible una busqueda mas flexible. El problema de
este query es que no funciona, da un error de sintaxis:
Msg 493, Level 16, State 1, Line 1
The column 'tRef' that was returned from the nodes() method cannot be used
directly. It can only be used with one of the four XML datatype methods
exist(), nodes(), query(), and value() or in IS NULL and IS NOT NULL checks.
Dado que no entiendo bien lo que hace ese query ni como funciona el cross
apply, no tengo idea de como sulucioanrlo.
Alguien puede darme una mano?
Gracias
Carlos Peix
