xml sql en español

7/31/2019 xml sql en espaol

1/24

Transacciones en Transact SQL

Concepto de transaccion

Una transaccin es un conjunto de operaciones Transact SQL que se ejecutan como un nico

bloque, es decir, si falla una operacin Transact SQL fallan todas. Si una transaccin tiene xito,todas las modificaciones de los datos realizadas durante la transaccin se confirman y se conviertenen una parte permanente de la base de datos. Si una transaccin encuentra errores y debecancelarse o revertirse, se borran todas las modificaciones de los datos.

El ejemplo clsico de transaccin es una transferencia bancaria, en la que quitamos saldo a unacuenta y lo aadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, nodebemos quitarlo de la cuenta de origen.

SQL Server funciona por defecto con Transacciones de confirmacin automtica, es decir,cada instruccin individual es una transaccin y se confirma automticamente.

Sobre el ejemplo anterior de la transferencia bancaria, un script debera realizar algo parecido alos siguiente:

DECLARE @importe DECIMAL(18,2),

@CuentaOrigenVARCHAR(12),

@CuentaDestinoVARCHAR(12)

/* Asignamos el importe de la transferencia

* y las cuentas de origen y destino

*/

SET @importe = 50

SET @CuentaOrigen ='200700000001'

SET @CuentaDestino ='200700000002'

/* Descontamos el importe de la cuenta origen */

UPDATE CUENTAS

SET SALDO = SALDO - @importe

WHERE NUMCUENTA = @CuentaOrigen


2/24

/* Registramos el movimiento */

INSERTINTO MOVIMIENTOS

(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO + @importe, SALDO, @importe,getdate()

FROM CUENTAS


/* Incrementamos el importe de la cuenta destino */

UPDATE CUENTAS

SET SALDO = SALDO + @importe

WHERE NUMCUENTA = @CuentaDestino



(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

SELECT

IDCUENTA, SALDO - @importe, SALDO, @importe,getdate()

FROM CUENTAS


Esta forma de actuar seria erronea, ya que cada instruccin se ejecutaria y confirmara de formaindependiente, por lo que un error dejara los datos erroneos en la base de datos ( y ese es el peor

error que nos podemos encontrar! )

Transacciones implicitas y explicitas

Para agrupar varias sentencias Transact SQL en una nica transaccin, disponemos de lossiguientes mtodos:


3/24

Transacciones explcitasCada transaccin se inicia explcitamente con la instruccin BEGIN TRANSACTION y se terminaexplcitamente con una instruccin COMMIT o ROLLBACK.

Transacciones implcitasSe inicia automtivamente una nueva transaccin cuando se ejecuta una instruccin que realizamodificaciones en los datos, pero cada transaccin se completa explcitamente con una

instruccin COMMIT o ROLLBACK.

Para activar-desactivar el modo de transacciones implicitas debemos ejecutar la siguienteinstruccin.

--Activamos el modo de transacciones implicitas

SETIMPLICIT_TRANSACTIONSON

--Desactivamos el modo de transacciones implicitas

SETIMPLICIT_TRANSACTIONSOFF

Cuando la opcinANSI_DEFAULTS est establecidaen ON, IMPLICIT_TRANSACTIONS tambin se establece en ON.

El siguiente ejemplo muestra el script anterior haciendo uso de transacciones explicitas.






*/

SET @importe = 50




4/24

BEGINTRANSACTION-- O solo BEGIN TRAN

BEGINTRY


UPDATE CUENTAS





(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,IMPORTE, FXMOVIMIENTO)

SELECT


FROM CUENTAS



UPDATE CUENTAS





(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,


5/24

IMPORTE, FXMOVIMIENTO)

SELECT


FROM CUENTAS


/* Confirmamos la transaccion*/

COMMITTRANSACTION-- O solo COMMIT

ENDTRY

BEGINCATCH

/* Hay un error, deshacemos los cambios*/

ROLLBACKTRANSACTION-- O solo ROLLBACK

PRINT'Se ha producido un error!'

ENDCATCH

El siguiente ejemplo muestra el mismo script con transacciones implicitas.






6/24



*/

SET @importe = 50



BEGINTRY


UPDATE CUENTAS






SELECT


FROM CUENTAS




7/24

UPDATE CUENTAS






SELECT


FROM CUENTAS


/* Confirmamos la transaccion*/

COMMITTRANSACTION-- O solo COMMIT

ENDTRY

BEGINCATCH

/* Hay un error, deshacemos los cambios*/

ROLLBACKTRANSACTION-- O solo ROLLBACK

PRINT'Se ha producido un error!'

ENDCATCH

La transaccin sigue activa hasta que emita una instruccin COMMIT o ROLLBACK. Una vezque la primera transaccin se ha confirmado o revertido, se inicia automticamente una nuevatransaccin la siguiente vez que la conexin ejecuta una instruccion para modificar datos.


8/24

La conexin contina generando transacciones implcitas hasta que se desactiva el modo detransacciones implcitas.

Podemos verificar el nmero de transacciones activas a travs de @@TRANCOUNT.


BEGINTRY

UPDATE CUENTAS SET FXALTA = FXALTA - 1

PRINT@@TRANCOUNT

COMMIT

ENDTRY

BEGINCATCH

ROLLBACK

PRINT'Error'

ENDCATCH

Otro punto a tener en cuenta cuando trabajamos con transacciones son los bloqueos y el nivelde aislamiento.Podemos aprender ms sobre bloqueos y nivel de aislamiento en este articulo .

Transacciones anidadas.

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instruccinCOMMIT afectar a la ltima transaccin abierta, pero ROLLBACK afectar a todas las transaccionesabiertas.

Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transaccin superior sedesharan tambin los cambios de todas las transacciones internas, aunque hayamos realizadoCOMMIT de ellas.

BEGINTRAN

UPDATE EMPLEADOS

SET NOMBRE ='Devjoker'
http://www.devjoker.com/contenidos/Articulos/210/Bloqueos-y-nivel-de-aislamiento-en-SQL-Server-2005.aspxhttp://www.devjoker.com/contenidos/Articulos/210/Bloqueos-y-nivel-de-aislamiento-en-SQL-Server-2005.aspxhttp://www.devjoker.com/contenidos/Articulos/210/Bloqueos-y-nivel-de-aislamiento-en-SQL-Server-2005.aspxhttp://www.devjoker.com/contenidos/Articulos/210/Bloqueos-y-nivel-de-aislamiento-en-SQL-Server-2005.aspx


9/24

WHERE ID=101

BEGINTRAN

UPDATE EMPLEADOS

SET APELLIDO1 ='Devjoker.COM'

WHERE ID=101

-- Este COMMIT solo afecta a la segunda transaccion.

COMMIT

-- Este ROLLBACK afecta a las dos transacciones.

ROLLBACK

Una consideracin a tener en cuanta cuando trabajamos con transacciones anidadas es laposibilidad de utilizar puntos de guardado o SAVEPOINTs.

Puntos de recuperacion (SavePoint).

Los puntos de recuperacin (SavePoints) permiten manejar las transacciones por pasos,pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transaccin.

El siguiente ejemplo muestra como trabajar con puntos de recuperacin.

BEGINTRAN

UPDATE EMPLEADOS


10/24

SET NOMBRE ='Devjoker'

WHERE ID=101

UPDATE EMPLEADOS

SET APELLIDO1 ='Devjoker.COM'

WHERE ID=101

SAVETRANSACTION P1 -- Guardamos la transaccion (Savepoint)

UPDATE EMPLEADOS

SET APELLIDO1 ='Otra cosa!'

WHERE ID=101

-- Este ROLLBACK afecta solo a las instrucciones

-- posteriores al savepoint P1.

ROLLBACKTRANSACTION P1

-- Confirmamos la transaccion

COMMIT

Using OPENXMLSQL Server 2000

11 out of 21 rated this helpful -Rate this topic

The examples in this topic show how OPENXML is used in creating a rowset view of an XML

document. For information about the syntax of OPENXML, seeOPENXML. The examples show all
http://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx#feedbackhttp://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx#feedbackhttp://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx#feedbackhttp://msdn.microsoft.com/en-us/library/aa276847(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa276847(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa276847(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa276847(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa226522(v=sql.80).aspx#feedback


11/24

aspects of OPENXML except specifying metaproperties in OPENXML. For more information about

specifying metaproperties in OPENXML, seeSpecifying Metaproperties in OPENXML.

Examples

In retrieving the data, rowpattern is used to identify the nodes in the XML document that determinethe rows. rowpattern is expressed in the XPath pattern language used in the MSXML XPath

implementation. For example, if the pattern ends in an element or an attribute, a row is created for

each element or attribute node selected by rowpattern.

The flags value provides default mapping. In the SchemaDeclaration, if no ColPattern is specified,

the mapping specified in flags is assumed. The flags value is ignored ifColPattern is specified

in SchemaDeclaration. The specified ColPattern determines the mapping (attribute-

centric or element-centric) and also the behavior in dealing with overflow and unconsumed data.

A. Execute a simple SELECT statement with OPENXML

The XML document in this example consists of the , , and

elements. The OPENXML statement retrieves customer information in a two-column rowset

(CustomerID and ContactName) from the XML document.

First, the sp_xml_preparedocument stored procedure is called to obtain a document handle. This

document handle is passed to OPENXML.

In the OPENXML statement:

rowpattern (/ROOT/Customer) identifies the nodes to process. The flags parameter value is set to 1 indicating attribute-centric mapping. As a result, the

XML attributes map to the columns in the rowset defined inSchemaDeclaration.

In SchemaDeclaration (in the WITH clause), the specified ColName values match thecorresponding XML attribute names. Therefore, the ColPattern parameter is not specified

in SchemaDeclaration.

And then, the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

DECLARE @idoc intDECLARE @doc varchar(1000)SET @doc ='
http://msdn.microsoft.com/en-us/library/aa226531(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa226531(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa226531(v=sql.80).aspxhttp://msdn.microsoft.com/en-us/library/aa226531(v=sql.80).aspx


12/24

'-- Create an internal representation of the XML document.

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT/Customer',1)

WITH (CustomerID varchar(10),ContactName varchar(20))

EXEC sp_xml_removedocument @idoc

This is the result:

CustomerID ContactName---------- --------------------VINET Paul HenriotLILAS Carlos Gonzlez

If the same SELECT statement is executed with flags set to 2, indicating element-centric mapping,

because elements do not have any subelements, the values

ofCustomerID and ContactName for both the customers are returned as NULL.

If in the XML document, the and are subelements, the element-

centric mapping retrieves the values.

DECLARE @idoc intDECLARE @doc varchar(1000)

SET @doc ='

VINETPaul Henriot

LILASCarlos Gonzlez

'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.SELECT *


13/24

FROM OPENXML (@idoc, '/ROOT/Customer',2)WITH (CustomerID varchar(10),

ContactName varchar(20))EXEC sp_xml_removedocument @idoc

This is the result:

CustomerID ContactName---------- --------------------VINET Paul HenriotLILAS Carlos Gonzlez

B. SpecifyColPattern for mapping between rowset columns and the XML attributes/elements

This example shows how the XPath pattern is specified in the optional ColPattern parameter to

provide mapping between rowset columns and the XML attributes (and elements).


elements. The OPENXML statement retrieves customer and order information as a rowset(CustomerID, OrderDate, ProdID, and Qty) from the XML document.




rowpattern (/ROOT/Customer/Order/OrderDetail) identifies the nodes toprocess.

For illustration purposes, the flags parameter value is set to 2 indicating element-centric mapping. However, the mapping specified in ColPattern overwrites this mapping

(the XPath pattern specified in ColPattern maps the columns in the rowset to attributes thus

resulting in an attribute-centric mapping).

In SchemaDeclaration (in the WITH clause), ColPattern is also specified with

the ColName and ColType parameters. The optional ColPattern is the XPath pattern specified to

indicate:

The OrderID, CustomerID, and OrderDate columns in the rowset map to the attributes ofthe parent of the nodes identified by rowpattern. rowpatternidentifies the

nodes. Therefore, the CustomerID and OrderDate columns map

to CustomerID and OrderDate attributes of the element.

The ProdID and Qty columns in the rowset map to the ProductID and Quantity attributesof the nodes identified in rowpattern.

And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML.

DECLARE @idoc int


14/24

DECLARE @doc varchar(1000)SET @doc ='

'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a 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')

This is the result:

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 1010283 LILAS 1996-08-16 00:00:00.000 72 3

The XPath pattern specified as ColPattern can also be specified to map the XML elements to the

rowset columns (resulting in element-centric mapping). In the following example, the XML

document and are subelements of element.

Because ColPattern overwrites the mapping specified in flags parameter, the flags parameter is not

specified in OPENXML.


10248VINET1996-07-04T00:00:00


15/24

10283LILAS1996-08-16T00:00:00

'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT stmt using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail')

WITH (CustomerID varchar(10) '../CustomerID',OrderDate datetime '../OrderDate',ProdID int '@ProductID',Qty int '@Quantity')


C. Combining attribute-centric and element-centric mapping

In this example, the flags parameter is set to 3, indicating that both attribute-centric and element-

centric mapping is to be applied. In this case, the attribute-centric mapping is applied first, and

then element-centric mapping is applied for all the columns not yet dealt with.


Paul Henriot

Carlos Gonzlez

'

-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT/Customer',3)

WITH (CustomerID varchar(10),ContactName varchar(20))


16/24


This is the result

CustomerID ContactName---------- --------------------

VINET Paul HenriotLILAS Carlos Gonzlez

The attribute-centric mapping is applied for CustomerID. There is no ContactName attribute in

the element; therefore, element-centric mapping is applied.

D. Specify text() XPath function as ColPattern

The XML document in this example consists of the and elements. The

OPENXML statement retrieves a rowset consisting of the oid attribute from the element,

the ID of the parent of the node (identified by rowpattern), and the leaf-value string of the element

content.




rowpattern (/root/Customer/Order) identifies the nodes to process. The flags parameter value is set to 1, indicating attribute-centric mapping. As a result, the

XML attributes map to the rowset columns defined inSchemaDeclaration.

In SchemaDeclaration (in the WITH clause), the rowset column names, oid and amount,match the corresponding XML attribute names. Therefore, theColPattern parameter is not

specified. For the comment column in the rowset, the XPath function (text()) is specified

as ColPattern. This overwrites theattribute-centric mapping specified in flags, and the

column contains the leaf-value string of the element content.


DECLARE @idoc intDECLARE @doc varchar(1000)--sample XML documentSET @doc ='

Customer was very

satisfied


17/24

ImportantHappy Customer.

'

-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/root/Customer/Order', 1)

WITH (oid char(5),amount float,comment ntext 'text()')


This is the result:

oid amount comment----- ----------- -----------------------------O1 3.5 NULLO2 13.4 Customer was very satisfiedO3 100.0 Happy Customer.O4 10000.0 NULL

E. SpecifyTableName in the WITH clause

This example specifies TableName in the WITH clause instead ofSchemaDeclaration in the WITH

clause. This is useful if you have a table with the structure you want and no column patterns

(ColPattern parameter) are required.

The XML document in this example consists of the and elements. The

OPENXML statement retrieves order information in a three-column rowset (oid, date, and amount)

from the XML document.




rowpattern (/root/Customer/Order) identifies the nodes to process. There is no SchemaDeclaration in the WITH clause. Instead, a table name is specified.

Therefore, the table schema is used as the rowset schema.

The flags parameter value is set to 1, indicating attribute-centric mapping. Therefore,attributes of the elements (identified by rowpattern) map to the rowset columns with the

same name.

And then the SELECT statement retrieves all the columns in the rowset provided by OPENXML.


18/24

-- Create a test table. This table schema is used by OPENXML as the-- rowset schema.CREATE TABLE T1(oid char(5), date datetime, amount float)DECLARE @idoc intDECLARE @doc varchar(1000)-- Sample XML documentSET @doc ='

Customer was very

satisfied

Important

'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/root/Customer/Order', 1)

WITH T1EXEC sp_xml_removedocument @idoc

This is the result:

oid date amount----- --------------------------- ----------O1 1996-01-20 00:00:00.000 3.5O2 1997-04-30 00:00:00.000 13.4O3 1999-07-14 00:00:00.000 100.0O4 1996-01-20 00:00:00.000 10000.0

F. Obtain the result in an edge table format

In this example, the WITH clause is not specified in the OPENXML statement. As a result, the rowset

generated by OPENXML has an edge table format. The SELECT statement returns all the columns in

the edge table.

The sample XML document in the example consists of the , , and

elements.





19/24

rowpattern (/ROOT/Customer) identifies the nodes to process. The WITH clause is not provided; therefore, OPENXML returns the rowset in an edge table

format.

And then the SELECT statement retrieves all the columns in the edge table.


'--Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT statement using OPENXML rowset provider.SELECT *FROM OPENXML (@idoc, '/ROOT/Customer')EXEC sp_xml_removedocument @idoc

The result is returned as an edge table. You can write queries against the edge table to obtaininformation:

The following query returns the number ofCustomer nodes in the document. Because theWITH clause is not specified, OPENXML returns an edge table. The SELECT statement

queries the edge table.

SELECT count(*) FROM OPENXML(@idoc, '/') WHERE localname = 'Customer' This query returns local names of XML nodes of element type. SELECT distinct localname FROM OPENXML(@idoc, '/') WHERE nodetype = 1 ORDER BY localname

G. Specifyrowpattern ending with an attribute


elements. The OPENXML statement retrieves order details information in a three-column rowset

(ProductID, Quantity, and OrderID) from the XML document.


20/24

First, the sp_xml_preparedocument is called to obtain a document handle. This document handle

is passed to OPENXML.


rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) ends with an XML attribute(ProductID). In the resulting rowset, a row is created for each attribute node selected in the

XML document.

In this example, the flags parameter is not specified. Instead, the mappings are specified bythe ColPattern parameter.

In SchemaDeclaration (in the WITH clause), ColPattern is also specified with

the ColName and ColType parameters. The optional ColPattern is the XPath pattern specified to

indicate:

The XPath pattern (.) specified as ColPattern for the ProdID column in the rowset identifiesthe context node (current node). As per the rowpattern specified, it isthe ProductID attribute of the element.

The ColPattern, ../@Quantity, specified for the Qty column in the rowset identifiesthe Quantity attribute of the parent () node of the context node

().

Similarly, the ColPattern, ../../@OrderID, specified for the OID column in the rowsetidentifies the OrderID attribute of the parent () of the parent () node

of the context node ().


DECLARE @idoc intDECLARE @doc varchar(1000)--Sample XML documentSET @doc ='

'-- Create an internal representation of the XML document.EXEC sp_xml_preparedocument @idoc OUTPUT, @doc-- Execute a SELECT stmt using OPENXML rowset provider.


21/24

SELECT *FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail/@ProductID')

WITH ( ProdID int '.',Qty int '../@Quantity',OID int '../../@OrderID')


This is the result:

ProdID Qty OID----------- ----------- -------11 12 1024842 10 1024872 3 10283

H. Specify an XML document with multiple text nodes

If you have multiple text nodes in an XML document, a SELECT statement with a ColPattern (text())

returns only the first text node instead of all. For example:

DECLARE @h intEXEC sp_xml_preparedocument @h OUTPUT,

N'TaU

',''

SELECT * FROM openxml(@h, '/root/b:Elem')WITH (Col1 varchar(20) 'text()')

The SELECT statement returns T as the result (and not TaU)

I. Retrieve individual values from multivalued attributes

An XML document can have attributes that are multivalued. For example the IDREFS attribute can

be multivalued. In an XML document, the multivalued attribute values are specified as a string with

the values separated by a space. In the following XML document, the attends attribute of the

element and theattendedBy attribute of are multivalued. Retrieving individual

values from a multivalued XML attribute and storing each value in a separate row in the database

requires additional work. This example shows the process.

This sample XML document consists of the following elements:

Consists ofid (student ID), name, and attends attributes. The attends attribute is a

multivalued attribute.


22/24

Consists ofid (class ID), name, and attendedBy attributes. The attendedBy attribute is a

multivalued attribute.

This attends attribute in and the attendedBy attribute in represent

a m:n relationship between Student and Class tables. A student can take many classes and a class

can have many students.

Assume you want to shred this document and save it in the database as follows:

Save the data in the Students table. Save the data in the Courses table. Save he m:n relationship data (between Student and Class) in the CourseAttendence table.

Additional work is required to extract the values. To retrieve this information and store it in

the table, use these stored procedures: Insert_Idrefs_Values

Inserts the values of course ID and student ID in the CourseAttendence table.

Extract_idrefs_valuesExtracts the individual student IDs from each element. An edge table is

used to retrieve these values.

Here are the steps:

1. Create the following tables:2. DROP TABLE CourseAttendance3. DROP TABLE Students4. DROP TABLE Courses5. GO6. CREATE TABLE Students(7. id varchar(5) primary key,8. name varchar(30)9. )10. GO11. CREATE TABLE Courses(12. id varchar(5) primary key,13. name varchar(30),14. taughtBy varchar(5)15. )16. GO17. CREATE TABLE CourseAttendance(18. id varchar(5) references Courses(id),19. attendedBy varchar(5) references Students(id),20. constraint CourseAttendance_PK primary key (id,

attendedBy)21. )


23/24

22. go23. Create these stored procedures:24. DROP PROCEDURE f_idrefs25. GO26. CREATE PROCEDURE f_idrefs27. @t varchar(500),28. @idtab varchar(50),29. @id varchar(5)30. AS31. DECLARE @sp int32. DECLARE @att varchar(5)33. SET @sp = 034. WHILE (LEN(@t) > 0)35. BEGIN36. SET @sp = CHARINDEX(' ', @t+ ' ')37. SET @att = LEFT(@t, @sp-1)38. EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''',

'''+@att+''')')39. SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp)40. END41. Go42.43. DROP PROCEDURE fill_idrefs44. GO45. CREATE PROCEDURE fill_idrefs46. @xmldoc int,47. @xpath varchar(100),48. @from varchar(50),49. @to varchar(50),50. @idtable varchar(100)51. AS52. DECLARE @t varchar(500)53. DECLARE @id varchar(5)54.55. /* Temporary Edge table */56. SELECT *57. INTO #TempEdge58. FROM OPENXML(@xmldoc, @xpath)59.60. DECLARE fillidrefs_cursor CURSOR FOR61. SELECT CAST(iv.text AS nvarchar(200)) AS id,62. CAST(av.text AS nvarchar(4000)) AS refs63. FROM #TempEdge c, #TempEdge i,64. #TempEdge iv, #TempEdge a, #TempEdge av65. WHERE c.id = i.parentid66. AND UPPER(i.localname) = UPPER(@from)67. AND i.id = iv.parentid68. AND c.id = a.parentid69. AND UPPER(a.localname) = UPPER(@to)70. AND a.id = av.parentid71.72. OPEN fillidrefs_cursor73. FETCH NEXT FROM fillidrefs_cursor INTO @id, @t74. WHILE (@@FETCH_STATUS -1)75. BEGIN76. IF (@@FETCH_STATUS -2)77. BEGIN


24/24

78. execute f_idrefs @t, @idtable, @id79. END80. FETCH NEXT FROM fillidrefs_cursor INTO @id, @t81. END82. CLOSE fillidrefs_cursor83. DEALLOCATE fillidrefs_cursor84. Go85. This is the sample document that is shredded and the data is stored in the preceding tables.86. DECLARE @h int87. EXECUTE sp_xml_preparedocument @h OUTPUT, '88. 89. 90. 91. 92. 93. 94. 95.96. 98. 100. 102. 103. 105. 107.'108.109.INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH

Students110.111.INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH

Courses112./* Using the edge table */113.EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby',

'CourseAttendance'114.115.SELECT * FROM Students116.SELECT * FROM Courses117.SELECT * FROM CourseAttendance118.

EXECUTE sp_xml_removedocument @h

xml sql en español

Documents