Lección 1: Trabajando con XML
XML es un lenguaje de marcas de texto jerárquico, fácilmente legible por el ser humano, que se utiliza habitualmente para el intercambio de datos dentro y entre sistemas. Debido a que el lenguaje XML es en sí un tema muy grande, que un conocimiento básico de XML es un requisito previo para esta lección.
Como referencia, un ejemplo simple de un documento XML que contiene dos clientes (denotada por la etiqueta <Customer>), cada uno con unas órdenes(denotada por la etiqueta <Order>), se muestra aquí:
<?xml version="1.0"?>
<Customers>
<Customer Id="1" AccountNumber="AW00000001" Type="S">
<Orders>
<Order Id="43860" OrderDate="2001-08-01T00:00:00" ShipDate="2001-08-08T00:00:00" />
<Order Id="44501" OrderDate="2001-11-01T00:00:00" ShipDate="2001-11-08T00:00:00" />
<Order Id="45283" OrderDate="2002-02-01T00:00:00" ShipDate="2002-02-08T00:00:00" />
<Order Id="46042" OrderDate="2002-05-01T00:00:00" ShipDate="2002-05-08T00:00:00" />
</Orders>
</Customer>
<Customer Id="2" AccountNumber="AW00000002" Type="S">
<Orders>
<Order Id="46976" OrderDate="2002-08-01T00:00:00" ShipDate="2002-08-08T00:00:00" />
<Order Id="47997" OrderDate="2002-11-01T00:00:00" ShipDate="2002-11-08T00:00:00" />
<Order Id="49054" OrderDate="2003-02-01T00:00:00" ShipDate="2003-02-08T00:00:00" />
<Order Id="50216" OrderDate="2003-05-01T00:00:00" ShipDate="2003-05-08T00:00:00" />
<Order Id="51728" OrderDate="2003-08-01T00:00:00" ShipDate="2003-08-08T00:00:00" />
<Order Id="57044" OrderDate="2003-11-01T00:00:00" ShipDate="2003-11-08T00:00:00" />
<Order Id="63198" OrderDate="2004-02-01T00:00:00" ShipDate="2004-02-08T00:00:00" />
<Order Id="69488" OrderDate="2004-05-01T00:00:00" ShipDate="2004-05-08T00:00:00" />
</Orders>
</Customer>
</Customers>
Como se ha mencionado, el ejemplo de código anterior es un documento XML. En esta lección, los fragmentos de XML también se analizan. Fragmentos de XML son similares a los documentos XML, la diferencia es que ellos no son en sí mismas un documento. La idea es que un fragmento de XML se supone que es parte de un documento XML, es decir, que se ha tomado fuera del contexto del documento. Esto significa que un fragmento de XML carece de la declaración XML(<? Xml...>) Y no tiene que tener un elemento raíz (como <Customers> en el ejemplo anterior). He aquí un ejemplo de un fragmento de XML:
<Order Id="43860" OrderDate="2001-08-01T00:00:00" ShipDate="2001-08-08T00:00:00" />
<Order Id="44501" OrderDate="2001-11-01T00:00:00" ShipDate="2001-11-08T00:00:00" />
<Order Id="45283" OrderDate="2002-02-01T00:00:00" ShipDate="2002-02-08T00:00:00" />
Si desea o no utilizar XML dentro de un sistema de base de datos relacional es a menudo objeto de debate. Como con la mayoría de características, cuándo y dónde utilizar depende del problema que está tratando de resolver. XML se puede utilizar para varias acciones relacionadas con una base de datos. Los usos principales se enumeran aquí:
- Recuperación de datos relacionales como XML: En lugar de recuperar un resultado tabular conjunto de la base de datos, recuperar un documento XML.
- Pasar los datos en formato XML a la base de datos: En lugar de pasar valores escalares a la base de datos mediante la emisión de varios idiomas manipulación de datos (DML) o se ejecuta un procedimiento almacenado varias veces, un documento o fragmento XML se puede pasar directamente a la base de datos.
- Almacenar y consultar un documento real o fragmento XML en la base de datos:
Recuperar datos de tablas como XML
Para empezar, ¿por qué desea recuperar un documento XML o un fragmento de la base de datos en lugar de un conjunto de resultados de tabla? Una razón podría ser simplemente que la persona a quien va a recuperar estos datos lo quiere como XML. Otra razón podría ser simplemente que los datos que están alcanzando se presta mejor a ser descrito mediante XML que utilizando una tabla. Esto es cierto para los datos jerárquicos, como el primer ejemplo en este capítulo con los clientes y sus respectivas órdenes.
Si lo piensas bien, es probable que sea muy común para una aplicación en busca de una jerarquía de datos como estos. Si usted no puede utilizar XML como datos para el formato, tiene otras dos opciones en SQL Server.
La primera opción es ejecutar dos sentencias SELECT separadas y combinar los clientes con sus respectivas órdenes en la aplicación cliente. Tenga en cuenta que es necesario incluir el CustomerID en ambas consultas para permitir la combinación de los resultados en el cliente:
SELECT c.CustomerID ,c.AccountNumber ,c.CustomerType FROM Sales.Customer AS c WHERE c.CustomerID IN (1, 2);
y su resultado:
SELECT soh.CustomerID ,soh.SalesOrderID ,soh.OrderDate ,soh.ShipDate FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID IN (1, 2);
y su resultado:
La segunda opción es para ejecutar un SELECT y recuperar las columnas que desee en cada tabla mediante una combinación. Esta opción aún depende de la aplicación cliente para determinar qué datos pertenecen a los clientes y qué datos pertenecen a la orden. En primer lugar, debe utilizar una combinacion LEFT OUTER JOIN para combinar las dos tablas y permitir a los clientes sin ordenes de pedidos ser retornados. En segundo lugar, la cláusula ORDER BY es útil(aunque no necesario) para simplificar la gestión de los resultados de la aplicación cliente, ya que todos los pedidos de un cliente en particular están garantizados para ser devueltos de forma secuencial. La consulta de unión es la siguiente:
SELECT c.CustomerID AS Customer_CustomerID ,c.AccountNumber AS Customer_AccountNumber ,c.CustomerType AS Customer_CustomerType ,soh.SalesOrderID AS Order_SalesOrderID ,soh.OrderDate AS Order_OrderDate ,soh.ShipDate AS Order_ShipDate FROM Sales.Customer AS c LEFT OUTER JOIN Sales.SalesOrderHeader AS soh ON soh.CustomerID = c.CustomerID WHERE c.CustomerID IN (1, 2) ORDER BY Customer_CustomerID;
En algunas columnas se redujo los titulos por cuestiones de espacio, pero el orden de los nombres es el descripto en la consulta
Ambas soluciones tabulares anteriores crean una buena cantidad de trabajo para la aplicación cliente. Así que, ¿por qué la solución XML es deseable? Debido a que el resultado tiene una base de jerarquías (los clientes que tienen órdenes) la recuperación del resultado como XML, que es jerárquica por definición, puede simplificar la tarea de la aplicación.
Como referencia, ver la sentencia SELECT se muestra aquí, que devuelve un resultado XML.
SELECT c.CustomerID AS "@Id" , c.AccountNumber AS "@AccountNumber" , c.CustomerType AS "@Type", (SELECT soh.SalesOrderID AS "@Id" ,soh.OrderDate AS "@OrderDate", soh.ShipDate AS "@ShipDate" FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID FOR XML PATH('Order'), TYPE) AS "Orders" FROM Sales.Customer AS c WHERE c.CustomerID IN (1, 2)FOR XML PATH('Customer'), ROOT('Customers');
dando como resultado esto:
Ahora echemos un vistazo rápido a cómo se puede ejecutar esta consulta desde una aplicación. NET, recuperar el XML en la memoria, y fácilmente a través del bucle obtener los clientes y sus respectivas órdenes. Para simplificar se utiliza en el ejemplo una aplicación de consola :
'primero en VB: Imports System Imports System.Xml Imports System.Data.SqlClient Module TK433 Sub Main() Using conn As SqlConnection = New SqlConnection( _ "server=.;database=AdventureWorks;trusted_connection=yes") Dim cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = _ "SELECT " & vbCrLf & _ " c.CustomerID AS ""@Id""" & vbCrLf & _ " ,c.AccountNumber AS ""@AccountNumber""" & vbCrLf & _ " ,c.CustomerType AS ""@Type""" & vbCrLf & _ " ,(" & vbCrLf & _ " SELECT" & vbCrLf & _ " soh.SalesOrderID AS ""@Id""" & vbCrLf & _ " ,soh.OrderDate AS ""@OrderDate""" & vbCrLf & _ " ,soh.ShipDate AS ""@ShipDate""" & vbCrLf & _ " FROM Sales.SalesOrderHeader AS soh" & vbCrLf & _ " WHERE(soh.CustomerID = c.CustomerID)" & vbCrLf & _ " FOR XML PATH('Order'), TYPE" & vbCrLf & _ " ) AS Orders" & vbCrLf & _ " FROM Sales.Customer AS c" & vbCrLf & _ "WHERE c.CustomerID IN (1, 2)" & vbCrLf & _ "FOR XML PATH('Customer'), ROOT('Customers')" conn.Open() ' Execute the query using an XML reader. Dim reader As XmlReader = cmd.ExecuteXmlReader() ' Use the XML reader to populate an XML document. Dim doc As XmlDocument = New XmlDocument() doc.Load(reader) ' Loop through the customers. For Each customer As XmlElement In doc.SelectNodes("/Customers/Customer") Console.WriteLine("Customer: {0}", customer.Attributes("Id").Value) For Each order As XmlElement In customer.SelectNodes("Orders/Order") Console.WriteLine(vbTab & "Order: {0}", order.Attributes("Id").Value) Next Next End Using Console.WriteLine("Press [ENTER] to exit. . .") Console.ReadLine() End Sub End Module
y Finalmente en C#:
//C#: using System; using System.Xml; using System.Data.SqlClient; class TK433Demo { static void Main() { using (SqlConnection conn = new SqlConnection( "server=.;database=AdventureWorks;trusted_connection=yes;")) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = @" SELECT c.CustomerID AS ""@Id"" ,c.AccountNumber AS ""@AccountNumber"" ,c.CustomerType AS ""@Type"" ,(SELECT soh.SalesOrderID AS ""@Id"" ,soh.OrderDate AS ""@OrderDate"" ,soh.ShipDate AS ""@ShipDate"" FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID FOR XML PATH('Order'), TYPE ) AS Orders FROM Sales.Customer AS c WHERE c.CustomerID IN (1, 2) FOR XML PATH('Customer'), ROOT('Customers');"; conn.Open(); // Execute the query using an XML reader to retrieve the results. XmlReader reader = cmd.ExecuteXmlReader(); // Use the XML reader to populate an XML document. XmlDocument doc = new XmlDocument(); doc.Load(reader); // Loop through the customers. foreach (XmlElement customer in doc.SelectNodes("/Customers/Customer")) { Console.WriteLine("Customer: {0}", customer.Attributes["Id"].Value); foreach (XmlElement order in customer.SelectNodes("Orders/Order")) { Console.WriteLine("\tOrder: {0}", order.Attributes["Id"].Value); } } } Console.WriteLine("Press [ENTER] to exit. . ."); Console.ReadLine(); } }
La simplicidad puede ser visto específicamente en el siguiente código, que recorre los clientes y pedidos:
'VB: For Each customer As XmlElement In doc.SelectNodes("/Customers/Customer") Console.WriteLine("Customer: {0}", customer.Attributes("Id").Value) For Each order As XmlElement In customer.SelectNodes("Orders/Order") Console.WriteLine(vbTab & "Order: {0}", order.Attributes("Id").Value) Next Next
//C#: foreach (XmlElement customer in doc.SelectNodes("/Customers/Customer")) { Console.WriteLine("Customer: {0}", customer.Attributes["Id"].Value); foreach (XmlElement order in customer.SelectNodes("Orders/Order")) { Console.WriteLine("\tOrder: {0}", order.Attributes["Id"].Value); } }
Como puede ver, ya que XML es jerárquica por naturaleza, no es necesario no perder de vista que los clientes o la orden que se está iterando, lo cual es administrado por la jerarquía del documento XML.
FOR XML <mode>
Como se vio en el ejemplo anterior, una cláusula adicional, FOR XML, se añade al final de la instrucción SELECT para producir un resultado XML. En SQL Server 2008, existen cuatro modos de la cláusula FOR XML: RAW, AUTO,EXPLICIT, y PATH. En el ejemplo anterior, el modo de PATH es utilizado. Este es el modo recomendado y el modo más poderoso.
FOR XML RAW
La sentencia SELECT. . . FOR XML RAW es la implementación más sencilla de FOR XML. Considere la posibilidad de la consulta se muestra aquí:
SELECT c.CustomerID
,c.AccountNumber
FROM Sales.Customer AS c
WHERE c.CustomerID IN (1,2)
FOR XML RAW;
Se puede ver en los resultados aquí que en su uso por defecto. Básicamente devuelve cada fila como un elemento XML y cada columna como un atributo XML (<row Column1=”. . .” Column2=”. . .” />).
Algunas de las mejoras se hicieron en SQL Server 2005 son que permite añadir el nombre de un elemento raíz, así como el nombre de los elementos creados para cada fila. Un ejemplo de esto se muestra en el siguiente bloque de código que utiliza la directiva ROOT, así como añade un parámetro en el modo RAW especificando el nombre del elemento (los cambios se muestran en negrita). También tenga en cuenta que al cambiar el nombre de la columna mediante el uso de un alias, también están cambiando el nombre del atributo (esto también fue apoyada en SQL Server 2000):
SELECT
c.CustomerID AS Id
,c.AccountNumber
FROM Sales.Customer AS c
WHERE c.CustomerID IN (1,2)
FOR XML RAW('Customer'), ROOT('Customers');
y este es el resultado:
Una pregunta que podría como se manejan los valores NULL en los resultados XML.La implementación por defecto es simplemente eliminar el atributo si el valor es NULL. Esto es bueno para la mayoría de las aplicaciones, pero algunas aplicaciones pueden diferenciar entre un valor perdido y NULL.
¿Cómo se pueden diferenciar? Consideremos el siguiente ejemplo:
Un XML describe un cliente en la base de datos que necesita ser actualizado. El cliente tiene 10 atributos que pueden existir en el elemento XML, pero sólo 2 de ellos existen en este particular elemento XML. Al pasar este elemento XML de la base de datos para realizar una actualización del cliente, la base de datos puede tomar una de dos acciones:
- Actualización de los 10 atributos, el establecimiento de ocho de ellos a NULL, ya que se han perdido y dos de ellos a sus nuevos valores.
- Actualizar sólo los dos atributos que existen en el elemento XML que se pasa a la base de datos y saltarse los 8 restantes, permitiéndoles mantener sus valores actuales.
Si elige la segunda opción, simplemente dejando los atributos de los elementos XML, si son realmente NULL no funcionará. De alguna manera debe definir explícitamente que en realidad son NULL (o deberían ser NULL). Esto es soportado a través de un elemento en XML llamado NIL. NIL en XML es igual a lo que se refiere a la base de datos como un valor NULL. Si un valor debe ser definido como NIL, no se puede almacenar en un atributo. Por el contrario, se debe almacenar como su propio elemento, ya que las necesidades de un atributo que lo define como NIL si son necesarias. Mira el siguiente ejemplo, donde el primer elemento que representa un producto de color a NULL. El utilizado anteriormente consulta FOR XML RAW se utilizó como referencia. Tenga en cuenta que el atributo de color que falta para el primer producto:
SELECT p.ProductID AS Id
,p.ListPrice ,p.Color
FROM Production.Product AS p
WHERE p.ProductID IN (514, 707)
FOR XML RAW('Product'), ROOT('Products');
El resultado seria esto:
Ahora considere el siguiente ejemplo, donde se agrega la directiva ELEMENTS para cada columna un elemento XML en lugar de un atributo. Tenga en cuenta que el elemento de color que aún falta para el primer producto:
SELECT p.ProductID AS Id
,p.ListPrice ,p.Color
FROM Production.Product AS p
WHERE p.ProductID IN (514, 707)
FOR XML RAW('Product'), ROOT('Products'), ELEMENTS;
este seria el resultado:
Por último, añada la directiva XSINIL a la directiva ELEMENTS para indicar a SQL Server que deberia manejar valores nulos manteniendo el los elementos XML para el valor perdido y estableciendo su atributo NIL en True. En este caso, se puede ver que el color es realmente NULL (o NIL) y no simplemente "desaparecido":
SELECT p.ProductID AS Id
,p.ListPrice ,p.Color
FROM Production.Product AS p
WHERE p.ProductID IN (514, 707)
FOR XML RAW('Product'), ROOT('Products'), ELEMENTS XSINIL;
Siendo este el resultado:
En el último ejemplo, también se puede ver que una referencia de espacio de nombres XML xsi y se hace referencia a http://www.w3.org/2001/XMLSchema-instance en el elemento raíz.
Esto es porque el atributo nil se define en este espacio de nombres. La referencia de espacio de nombres siempre se agrega si se utiliza la directiva XSINIL. Antes de pasar a la siguiente para el modo de XML, AUTO, usted debe saber que tanto los elementos y directrices XSINIL existen para el modo AUTO, pero no para los otros dos modos, EXPLICIT y PATH.
FOR XML AUTO
El modo AUTO se diferencia de RAW, ya que soporta de forma nativa las jerarquías. Sin embargo, las jerarquías tienen que ser simples porque AUTO no es compatible con más de un camino de ramas.
Por ejemplo, la jerarquía de las siguientes obras:
Customer
Order
Order row
Pero para la siguiente no funciona, ya que tiene varias rutas:
Customer
Order
Order row
Contacts
En el modo AUTO, cada tabla incluida en la consulta obtiene su propio elemento de la jerarquía y el nombre del elemento se deriva de la tabla de alias utilizado en la consulta. La jerarquía se crea a partir de la orden de las columnas devueltas por la consulta, no el orden de las tablas.
Observar que la consulta en el siguiente ejemplo devuelve a los clientes con pedidos con FOR XML AUTO. Tenga en cuenta que la unión se lleva a cabo desde el pedido al cliente, pero el cliente sigue estando por encima del orden en la jerarquía. Esto se debe a las columnas de los clientes se muestran antes de las columnas de orden en la lista de columnas de la cláusula SELECT y cada elemento de los clientes se repite unas cuantas veces en el resultado XML. Esto se debe a que la jerarquía se construye también a partir de la orden de las filas en el resultado. Eso significa que usted debe asegurarse de que la cláusula ORDER BY se usa correctamente en cada grupo para el entorno de su cliente (en este ejemplo, ordenar las filas de un identificador único / columna GUID de la tabla para obtener una clasificación aleatoria de aspecto)
SELECT Customer.CustomerID AS Id
,Customer.AccountNumber
,"Order".SalesOrderID
,"Order".rowguid AS RowGuid
FROM Sales.SalesOrderHeader AS "Order"
RIGHT OUTER JOIN Sales.Customer AS Customer
ON Customer.CustomerID = "Order".CustomerID
WHERE Customer.CustomerID IN (1,2)
ORDER BY "Order".rowguid
FOR XML AUTO, ROOT('Customers');
y el resultado seria el siguiente:
Este es el resultado tabular (creado por omitiendo la parte XML de la consulta):
Para solucionar el problema se muestra en el ejemplo anterior, es obvio que sólo hay que ordenar los resultados correctamente. Esto puede sonar simple, pero es una realidad insidiosa, y es muy facil que un error pueda colarse en el código si no tienes cuidado. En el siguiente ejemplo, a resolver el problema de la clasificación por CustomerID con el grupo todos los pedidos juntos que pertenecen a un cliente específico.
Aquí, un pequeño truco utilizando una tabla derivada denominada Pedidos y crear un elemento XML entre el cliente y los elementos de orden se utiliza:
SELECT Customer.CustomerID AS Id ,Customer.AccountNumber ,Orders.X ,"Order".SalesOrderID ,"Order".rowguid AS RowGuid FROM Sales.SalesOrderHeader AS "Order" RIGHT OUTER JOIN Sales.Customer AS Customer ON Customer.CustomerID = "Order".CustomerID CROSS JOIN (SELECT NULL AS X) AS Orders WHERE Customer.CustomerID IN (1,2) ORDER BY Customer.CustomerID FOR XML AUTO, ROOT('Customers');
El resultado en XML seria:
Este es el resultado tabular (creado por omitiendo la parte XML de la consulta):
FOR XML EXPLICIT
El tercer modo de XML es explícito, que es difícil de escribir y aún más difícil de mantener. El hecho interesante sobre el modo EXPLICIT es que se puede crear virtualmente cualquier estructura XML. En esencia, para crear un documento XML mediante FOR XML EXPLICIT, se debe devolver un conjunto de resultados específicos, es decir, debe el nombre de las columnas de una manera específica. Al igual que el modo AUTO, también debe ordenar los resultados de manera apropiada para alcanzar el resultado deseado El conjunto de resultados del modo EXPLICIT debe contener dos columnas llamadas Tag y Parent. En la columna Tag , se agrega un identificador entero para cada elemento XML que desea regresar, y en la columna Parent , se especifica el identificador de la etiqueta del elemento XML que es el padre del elemento. Si el elemento no tiene un padre (Parent), se especifica NULL en la columna de los padres. El resto de las columnas en el conjunto de resultados se utilizan para definir tanto los nombres y valores de los elementos asi como los atributos que deben ser devuelto. El siguiente ejemplo muestra cómo se puede crear el cliente para utilizar el modo explícito:
SELECT 1 AS Tag ,NULL AS Parent ,NULL AS "Customers!1!!element" ,NULL AS "Customer!2!Id" ,NULL AS "Customer!2!AccountNumber" ,NULL AS "Order!3!Id" ,NULL AS "Order!3!OrderDate" UNION ALL SELECT 2 AS Tag ,1 AS Parent ,NULL AS "Customers!1!!element" ,c.CustomerID AS "Customer!2!Id" ,c.AccountNumber AS "Customer!2!AccountNumber" ,NULL AS "Order!3!Id" ,NULL AS "Order!3!OrderDate" FROM Sales.Customer AS c WHERE c.CustomerID IN (1,2) UNION ALL SELECT 3 AS Tag ,2 AS Parent ,NULL AS "Customers!1!!element" ,soh.CustomerID AS "Customer!2!Id" ,NULL AS "Customer!2!AccountNumber" ,soh.SalesOrderID AS "Order!3!Id" ,soh.OrderDate AS "Order!3!OrderDate" FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID IN (1,2) ORDER BY "Customer!2!Id", Tag FOR XML EXPLICIT;
Y este seria el resultado:
FOR XML PATH
El modo de FOR XML PATH es la mejor opción de los diferentes modos FOR XML para la mayoría de las soluciones. El modo PATH permite la fácil creación de diferentes estructuras XML simplemente interpretando los nombres de columna se especifica mediante una expresión XPath, como cuando se genera el resultado XML.
Considere la siguiente consulta:
SELECT c.CustomerID AS "@Id" ,c.AccountNumber AS "@AccountNumber" ,c.RowGuid AS "comment()" ,CAST('<Test/>' AS XML) AS "node()" ,c.CustomerType AS "AdditionalInfo/@Type" ,c.ModifiedDate AS "AdditionalInfo/text()" ,c.rowguid AS "node()" FROM Sales.Customer AS c WHERE c.CustomerID IN (1, 2) FOR XML PATH('Customer'), ROOT('Customers');
y este es el resultado:
En el resultado XML, se puede ver lo siguiente:
- La columna de @Id dio como resultado la identificación de atributos en el elemento del cliente.
- La columna @AccountNumber dio lugar al atributo AccountNumber en el Cliente elemento.
- La Columna comment() resultó en el valor de la columna RowGuid y se devuelven como un comentario XML.
- La columna node() dio lugar a la constante de XML en la consulta y se realiza directamente en el resultado XML sin terminar en un subelemento.
- La Columna AdditionalInfo/@Type da como resultado el atributo en el subelemento AdditionalInfo .
- La columnaAdditionalInfo/text()resultó en el texto del subelemento AdditionalInfo que se establece.
Este modo es mucho más poderoso que los dos modos RAW y AUTO, ya que le permite añadir atributos y elementos secundarios para la producción, así como la inclusión de otros tipos de construcciones XML. El modo PATH es también mucho más fácil de usar y fácil de leer que una consulta que utiliza el modo EXPLICIT.
Anidación para consultas XML
Todas las consultas del modo FOR XML se pueden anidar para producir una jerarquía. Esto significa que usted puede realizar una consulta FOR XML como una subconsulta en otra consulta FOR XML para producir un documento XML completo: Considere la siguiente consulta y el resultado:
SELECT c.CustomerID AS "@Id" ,c.AccountNumber AS "@AccountNumber" ,c.CustomerType AS "@Type" ,(SELECT TOP(2) -- Included to limit the size of the XML result. soh.SalesOrderID AS "@Id" ,soh.OrderDate AS "@OrderDate" ,soh.ShipDate AS "@ShipDate" ,(SELECT TOP(2) -- Included to limit the size of the XML result. sod.ProductID AS "@ProductId" ,sod.OrderQty AS "@Quantity" FROM Sales.SalesOrderDetail AS sod WHERE sod.SalesOrderID = soh.SalesOrderID FOR XML PATH('OrderDetail'), TYPE ) FROM Sales.SalesOrderHeader AS soh WHERE soh.CustomerID = c.CustomerID FOR XML PATH('Order'), TYPE ) AS "Orders" FROM Sales.Customer AS c WHERE c.CustomerID = 1 FOR XML PATH('Customer');
y este es el resultado:
Tenga en cuenta que la opción TYPE es requerido en cada subconsulta. Sin la opción TYPE, SQL Server interpreta el resultado de la subconsulta como una cadena codificada en XML. El siguiente XML sería devuelto si la opción TYPE fueron removidos de las subconsultas en el ejemplo anterior:
Finalmente, es posible añadir un espacio de nombres XML para el resultado mediante la inclusión de la cláusula WITH XMLNAMESPACES en la consulta. En el siguiente ejemplo, el espacio de nombres http://www.contoso.com/CustomerSchema se agrega como el espacio de nombres predeterminado para el documento XML, y el espacio de nombres http://www.contoso.com/CustomerSchemaV2 se añade bajo el alias v2 (el subelemento AdditionalInfo es el único elemento que utiliza el alias v2):
WITH XMLNAMESPACES(
DEFAULT 'http://www.contoso.com/CustomerSchema'
,'http://www.contoso.com/CustomerSchemaV2' AS v2)
SELECT
c.CustomerID AS "@Id"
,c.AccountNumber AS "@AccountNumber"
,c.CustomerType AS "@Type"
,c.ModifiedDate AS "v2:AdditionalInfo/@ModifiedDate"
FROM Sales.Customer AS c
WHERE c.CustomerID IN (1, 2)
FOR XML PATH('Customer'), ROOT('Customers');
y este es el resultado:
Utilizando el tipo de datos XML
SQL Server 2008 incluye el tipo de datos XML, que puede ser utilizado para almacenar los fragmentos y documentos XML. Internamente, el tipo de datos XML se almacena usando el tipo de datos varbinary (max), es decir, el XML no se almacena como una cadena de texto sino más bien como una representación binaria de un documento o fragmento XML.
El tipo de datos XML puede ser con o sin tipo. Esto simplemente significa que una colección de esquemas XML se asigna al tipo de verificar su contenido.
El siguiente ejemplo muestra la creación de una colección de esquemas XML y una tabla con columnas XML con y sin tipo asignado. Tenga en cuenta que la columna XML con tipo incluye el uso de la opción Documento para forzar la columna para apoyar sólo los documentos XML (y no fragmentos):
CREATE XML SCHEMA COLLECTION BooksSchemaCollection
AS N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.contoso.com/BooksSchema"
xmlns="http://www.contoso.com/BooksSchema"
elementFormDefault="qualified">
<xs:element name="Book">
<xs:complexType>
<xs:attribute name="Title" type="xs:string"/>
<xs:attribute name="Price" type="xs:decimal"/>
</xs:complexType>
</xs:element>
</xs:schema>';
GO
CREATE TABLE Test.Person
( PersonID INT IDENTITY PRIMARY KEY
,Name NVARCHAR(50) NOT NULL
,FavoriteBookUntypedXml XML NULL
,FavoriteBookTypedXml XML(DOCUMENT BooksSchemaCollection) NULL);
Ahora echemos un vistazo a las instrucciones DML siguientes. Tenga en cuenta que la actualización de una columna escrita con datos no válidos (en este caso, un libro con un precio válido de FortyFive en lugar de 45) recibe un mensaje de error de validación XML, pero los datos no válidos igual está permitido en el columna XML sin tipo:
INSERT Test.Person (Name) VALUES ('Jane Dow'); -- tendrá éxito: UPDATE Test.Person SET FavoriteBookUntypedXml = '<Book xmlns="http://www.contoso.com/BooksSchema" Title="The Best Book" Price="FortyFive"/>' WHERE PersonID = 1; -- NO tendrá éxito: UPDATE Test.Person SET FavoriteBookTypedXml = '<Book xmlns="http://www.contoso.com/BooksSchema" Title="The Best Book" Price="FortyFive"/>' WHERE PersonID = 1; -- tendrá éxito: UPDATE Test.Person SET FavoriteBookTypedXml = '<Book xmlns="http://www.contoso.com/BooksSchema" Title="The BestBook" Price="45"/>' WHERE PersonID = 1; Aquí están los resultados: (1 row(s) affected) (1 row(s) affected) Msg 6926, Level 16, State 1, Line 3 XML Validation: Invalid simple type value: 'FortyFive'. Location: /*:Book[1]/@*:Price (1 row(s) affected)
Una nota importante es que hay un problema con el uso de columnas de tipo XML, y que es el hecho de que la estructura de datos puede cambiar. Si necesita cambiar el esquema (por ejemplo, añadiendo más atributos de un elemento), primero debe cambiar todas las columnas que utiliza el esquema a utilizar en lugar de XML sin tipo. A continuación, debe quitar la colección de esquemas, vuelva a crear con los atributos agregados, y, finalmente, modificar las columnas de nuevo a utilizar el esquema. El problema no es sólo que es mucho trabajo para crear las secuencias de comandos para llevar a cabo este cambio (aunque eso es bastante malo).
También crea un montón de trabajo para SQL Server porque el código XML sin tipo asignado y escrito tienen diferentes estructuras internas (por ejemplo, todos los datos en XML sin tipo se almacena en forma de cadenas, mientras que en XML con tipo, los datos se almacenan utilizando el tipo de datos real seleccionado en el esquema) y SQL Server, debe convertir todos los datos del tipo de sin tipo asignado y luego de nuevo cada vez que quiera cambiar el esquema XML.
He aquí un ejemplo que elimina el esquema de la columna y luego vuelve a adjuntar es el siguiente:
ALTER TABLE Test.Person
ALTER COLUMN FavoriteBookTypedXml XML NULL;
GO
ALTER TABLE Test.Person
ALTER COLUMN FavoriteBookTypedXml XML(DOCUMENT BooksSchemaCollection) NULL;
Trabajar con XML almacenados en una variable o una columna XML
Cuando usted tiene los datos almacenados usando un tipo de datos XML, que quiere tanto a consultar y modificar la misma.
Esto se realiza mediante unos métodos proporcionados por el tipo de datos XML.
El metodo EXIST
El método exist devuelve un valor bit y se utiliza para verificar si una expresión XPath se encuentra dentro de una instancia XML. El siguiente ejemplo muestra una consulta simple en contra de la columna Demographics que utiliza el método exist para encontrar todas las encuestas con un valor de más de 5.000 en la columna TotalPurchaseYTD. También utiliza la función XPath xs:decimal para convertir el elemento a un valor decimal. (Tenga en cuenta que esta conversión no es necesaria cuando se utiliza XML con SQL Server, porque se deriva del esquema XML que el elemento es en realidad un decimal.)
WITH XMLNAMESPACES(DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
SELECT
COUNT(*)
FROM Sales.Individual
WHERE Demographics.exist(
'/IndividualSurvey/TotalPurchaseYTD[xs:decimal(.) > 5000]') = 1;
El metodo VALUE
El método value se utiliza para realizar una consulta XQuery en la instancia XML en busca de un solo valor escalar de la misma. En este ejemplo, la función XPath count se utiliza para calcular el número de elementos IndividualSurvey en la columna XML Demographics, y la suma la función de agregado de SQL se utiliza para resumir los cargos devueltos por el método value:
WITH XMLNAMESPACES(DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey')
SELECT
SUM(Demographics.value('count(/IndividualSurvey)', 'INT')) AS NumberOfIndividualSurveys
FROM Sales.Individual;
Tenga en cuenta que el segundo argumento del método value indica qué tipo de datos SQL es el valor recuperado de un documento XML que se debe convertir en el resultado.
El metodo NODES
El método nodes se utiliza para desmenuzar el XML en forma de tabla. En el siguiente ejemplo, cada elemento encontrado Orden se devuelve como una fila separada en el resultado de la consulta. Además de utilizar el método nodes , el código también se utiliza el método de valor para recuperar los valores específicos del documento XML en las columnas resultantes. Tenga en cuenta que este método se puede utilizar con un INSERT. . . SELECT o una instrucción UPDATE para varios valores de SQL Server en un solo parámetro:
DECLARE @Orders XML;
SET @Orders = N'
<Orders>
<Order Product="Bike" Quantity="1"/>
<Order Product="Bike" Quantity="2"/>
<Order Product="Car" Quantity="4"/>
</Orders>';
SELECT tab.col.value('@Product', 'NVARCHAR(50)') AS Product
,tab.col.value('@Quantity', 'INT') AS Quantity
,tab.col.value('count(../Order)', 'INT') AS TotalNumberOfOrders
FROM @Orders.nodes('/Orders/Order[xs:integer(@Quantity) > 1]') AS tab(col);
Nótese el uso de la función Count y la parent path (..) que se utiliza para crear la columna TotalNumberOfOrders. Esto puede ser muy útil, pero tenga en cuenta que el uso de parent paths en una llamada al método value de un resultado del método nodes puede degradar el rendimiento de manera significativa.
El metodo QUERRY
El método query se utiliza para realizar una consulta XQuery en la instancia XML para recuperar un fragmento de XML en lugar de un valor escalar o un resultado tabular. En el método Query, se utilizan expresiones del lenguaje XQuery es FLWOR para recuperar las partes del documento XML que usted necesita y lo presentará en la forma que desee. XQuery expresiones FLWOR en realidad se puede utilizar en otros métodos de tipo de datos XML, así, pero se suele utilizar en el contexto del método de consulta.
Una expresión FLWOR consiste en las keywords for, let, return, order by y where, donde equivale aproximadamente a FROM en SQL, que es aproximadamente igual a una expresión de tabla común (CTE) la declaración, y el rendimiento es aproximadamente igual a SELECT propio de SQL, y ORDER BY y WHERE son iguales a sus homónimos SQL.
El ejemplo siguiente utiliza una expresión FLWOR para devolver todas las órdenes de una cantidad de dos o más a partir del documento XML, ordenados de acuerdo con la mayor cantidad que se devuelve primero y los elementos XML reales que se tradujeron al sueco:
DECLARE @Orders XML;
SET @Orders = N'
<Orders>
<Order Product="Bike" Quantity="1"/>
<Order Product="Bike" Quantity="2"/>
<Order Product="Car" Quantity="4"/>
</Orders>';
SELECT @Orders.query('
<Beställningar>
{
for $o in /Orders/Order
where xs:decimal($o/@Quantity) >= 2
order by xs:decimal($o/@Quantity) descending
return <Beställning Produkt="{data($o/@Product)}" Antal="{data($o/@Quantity)}"/>
}
</Beställningar>
');
Resumen de la lección
- XML se puede generar mediante una instrucción SELECT en cuatro modos diferentes: FOR XML RAW, FOR XML AUTO, FOR XML PATH, y FOR XML EXPLICIT.
- FOR XML PATH suele ser el el modo preferido para generar XML.
El tipo de datos XML puede ser con tipo (validado por una colección de esquemas XML) o sin tipo. - En un tipo de datos XML sin tipo asignado, todos los valores se interpretan siempre como cadenas.
- Usted puede usar el value,query, exist,nodes y modificar métodos para consultar y modificar las instancias del tipo de datos XML.
No hay comentarios:
Publicar un comentario