OPENXML (Transact-SQL)
OPENXML proporciona una vista de un conjunto de filas en un documento XML. Puesto que OPENXML es un proveedor de conjuntos de filas, puede utilizarse en instrucciones Transact-SQL en las que pueden aparecer proveedores de conjuntos de filas como una tabla, vista o la función OPENROWSET.
.gif "Icono de vínculo a temas")
Convenciones de sintaxis de Transact-SQL
Sintaxis
OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Argumentos
idoc
Es el identificador del documento de la representación interna de un documento XML. La representación interna de un documento XML se crea llamando a sp_xml_preparedocument.rowpattern
Es el patrón XPath utilizado para identificar los nodos (en el documento XML cuyo identificador se pasa en el parámetro idoc) que se van a procesar como filas.flags
Indica la asignación que debe utilizarse entre los datos XML y el conjunto de filas relacional, y cómo debe llenarse la columna de desbordamiento; flags es un parámetro de entrada opcional y puede tomar uno de los valores siguientes.Valor del byte
Descripción
0
Establece como valor predeterminado la asignación attribute-centric.
1
Usa la asignación attribute-centric. Se puede combinar con XML_ELEMENTS. En ese caso, primero se aplica la asignación attribute-centric y, a continuación, la asignación element-centric en todas las columnas que todavía no se han visto afectadas.
2
Usa la asignación element-centric. Se puede combinar con XML_ATTRIBUTES. En ese caso, primero se aplica la asignación attribute-centric y, a continuación, la asignación element-centric en todas las columnas que todavía no se han visto afectadas.
8
Puede combinarse (OR lógico) con XML_ATTRIBUTES o XML_ELEMENTS. Si se trata de una recuperación, esta marca informa de que los datos consumidos no se deberían copiar a la propiedad de desbordamiento @mp:xmltext.
SchemaDeclaration
Es la definición de esquema de la forma: ColName ColType [ColPattern | MetaProperty] [, ColNameColType [ColPattern | MetaProperty]...]ColName
Es el nombre de columna en el conjunto de filas.ColType
Es el tipo de datos SQL Server de la columna en el conjunto de filas. Si los tipos de columna son distintos del tipo de datos xml subyacente del atributo, se producirá una conversión de tipos.ColPattern
Es un parámetro opcional, un patrón XPath general que describe la forma de asignar los nodos XML a las columnas. Si no se especifica ColPattern, se realiza la asignación predeterminada (asignación (attribute-centric o element-centric especificada en los valores flags).El patrón XPath especificado como ColPattern se utiliza para especificar la naturaleza especial de la asignación (en caso de una asignación attribute-centric y element-centric ) que sobrescribe o mejora la asignación predeterminada especificada por los indicadores flags.
El patrón XPath general especificado como ColPattern también admite las metapropiedades.
MetaProperty
Es una de las metapropiedades que proporciona OPENXML. Si se especifica MetaProperty, la columna contiene información que proporciona la metapropiedad. Las metapropiedades permiten extraer información (como información sobre el espacio de nombres y la posición relativa) acerca de nodos XML. Esto proporciona más información que la que se puede ver en la representación de texto.
TableName
Es el nombre de tabla que puede proporcionarse (en lugar de SchemaDeclaration) si ya existe una tabla con el esquema deseado y no se requiere patrones de columna.
Comentarios
La cláusula WITH proporciona un formato de conjunto de filas (e información de asignación adicional si es necesario) mediante SchemaDeclaration o especificando un TableName existente. Si no se especifica la cláusula opcional WITH, los resultados se devuelven en un formato de tabla irregular. Las tablas irregulares representan la estructura refinada del documento XML (por ejemplo, los nombres de elementos o atributos, la jerarquía del documento, los espacios de nombre, las instrucciones de proceso, etc.) en una única tabla.
En la siguiente tabla se describe la estructura de la tabla irregular.
Nombre de columna |
Tipo de datos |
Descripción |
|---|---|---|
id |
bigint |
Es el id. único del nodo del documento. El elemento raíz tiene un valor de identificador 0. Los valores de identificador negativos están reservados. |
parentid |
bigint |
Identifica el elemento primario del nodo. El elemento primario especificado por este identificador no es necesariamente el elemento primario, pero depende del NodeType del nodo cuyo elemento primario indica este identificador. Por ejemplo, si se trata de un nodo de texto, su elemento primario puede ser un nodo de atributo. Si el nodo está en el nivel superior del documento XML, su ParentID es NULL. |
nodetype |
int |
Identifica el tipo de nodo. Es un entero que se corresponde con la numeración del tipo de nodo XML DOM. Los tipos de nodo son: 1 = Nodo de elemento 2 = Nodo de atributo 3 = Nodo de texto |
localname |
nvarchar |
Proporciona el nombre local del elemento o atributo. Es NULL si el objeto DOM no tiene nombre. |
prefijo |
nvarchar |
Es el prefijo del espacio del nombre del nodo. |
namespaceuri |
nvarchar |
Es el URI del espacio de nombres del nodo. Si el valor es NULL, no hay ningún espacio de nombres. |
datatype |
nvarchar |
Es el tipo de datos reales de la fila del elemento o atributo y, en caso contrario, es NULL. El tipo de datos se infiere a partir de las DTD insertadas o del esquema insertado. |
prev |
bigint |
Es el id. XML del anterior elemento del mismo nivel. Es NULL si no existe ningún elemento previo directo del mismo nivel. |
text |
ntext |
Contiene el valor de atributo o el contenido de elemento en forma de texto (o es NULL si la entrada de la tabla irregular no se requiere un valor). |
Ejemplos
A.Usar una instrucción SELECT simple con OPENXML
En el siguiente ejemplo se crea una representación interna de la imagen XML utilizando sp_xml_preparedocument. A continuación se ejecuta una instrucción SELECT que usa un proveedor del conjunto de filas OPENXML contra la representación interna del documento XML.
El valor de flag se establece en 1. Esto indica la asignación attribute-centric. Por tanto, los atributos XML se asignan a las columnas del conjunto de filas. El rowpattern especificado como /ROOT/Customer identifica los nodos <Customers> que se van a procesar.
El parámetro opcional ColPattern (patrón de columna) no se especifica porque el nombre de columna coincide con los nombres de atributos XML.
El proveedor del conjunto de filas OPENXML crea un conjunto de filas de 2 columnas (CustomerID y ContactName) desde el que la instrucción SELECT recupera las columnas necesarias (en este caso, todas las columnas).
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
El conjunto de resultados es el siguiente.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Si se ejecuta la misma instrucción SELECT con el parámetro flags establecido en 2, lo que indica una asignación centrada en elementos, los valores de CustomerID y ContactName para los dos clientes en el documento XML se devuelven como NULL ya que no hay ningún elemento con el nombre CustomerID o ContactName en el documento XML.
El conjunto de resultados es el siguiente.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B.Especificar ColPattern para la asignación entre columnas y los atributos XML
En la siguiente consulta se devuelven los atributos de CustomerID, OrderDate, ProductID y Quantity del documento XML. rowpattern identifica los elementos <OrderDetails>. ProductID y Quantity son los atributos del elemento <OrderDetails>. No obstante, OrderID, CustomerID y OrderDate son los atributos del elemento primario (<Orders>).
Se especifica el parámetro opcional colpattern. Indica lo siguiente:
Las columnas OrderID, CustomerID y OrderDate del conjunto de filas se asignan a los atributos del elemento primario de los nodos identificados por rowpattern en el documento XML.
La columna ProdID del conjunto de filas se asigna al atributo ProductID, y la columna Qty del conjunto de filas se asigna al atributo Quantity de los nodos identificados en rowpattern.
Aunque el parámetro flags especifica la asignación element-centric, la asignación especificada en ColPattern sobrescribe la anterior.
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
El conjunto de resultados es el siguiente.
OrderID CustomerID OrderDate ProdID Qty
------------------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
C.Obtener el resultado en formato de tabla irregular
El documento XML del ejemplo siguiente está formado por los elementos <Customers>, <Orders> y <Order_0020_Details>. Primero se llama a sp_xml_preparedocument para obtener un identificador de documentos. Este identificador de documentos se pasa a OPENXML.
En la instrucción OPENXML, el rowpattern (/ROOT/Customers) identifica los nodos <Customers> que se van a procesar. Puesto que no se proporciona la cláusula WITH, OPENXML devuelve el conjunto de filas en un formato de tabla irregular.
Finalmente, la instrucción SELECT recupera todas las columnas de la tabla irregular.
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;