====== SQL SERVER 2012 XML ======

[[https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server?view=sql-server-ver15]]

Consultas al XML [[https://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column]]

[[https://www.red-gate.com/simple-talk/sql/database-administration/manipulating-xml-data-in-sql-server/]]

[[https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-xml-methods-in-sql-server/]]

[[https://www.sqlshack.com/working-with-xml-data-in-sql-server/]]

[[https://www.c-sharpcorner.com/UploadFile/rohatash/openxml-function-in-sql-server-2012/]]

[[https://www.c-sharpcorner.com/UploadFile/rohatash/query-and-node-method-with-xml-type-variable-in-sql-server-2/]]

[[https://sqlwithmanoj.com/2011/07/13/select-or-query-nodes-in-hierarchial-or-nested-xml/]]

Graba el archivo XML

[[https://stackoverflow.com/questions/1803911/generating-xml-file-from-sql-server-2008]]

====== XML ======

<code xml>

SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM
OPENROWSET(BULK 'D:\BK\MCI_0403_5767_0536275007.XML', SINGLE_BLOB) AS XML

</code>

  * [[https://docs.microsoft.com/en-us/sql/relational-databases/xml/load-xml-data?view=sql-server-ver15]]
  * [[https://docs.microsoft.com/en-us/sql/relational-databases/xml/openxml-sql-server?view=sql-server-ver15]]



Ejemplo para abrir XML.html de un archivo:
del file to XML:
  * [[https://docs.microsoft.com/en-us/sql/relational-databases/xml/load-xml-data?view=sql-server-ver15]]
<code sql>

SELECT *
    FROM OPENROWSET (BULK 'C:\enviosEERS01Alias_alias_235_-_2019_-_8607.htm', SINGLE_CLOB)
AS xCol

</code>

<code sql>
--INSERT INTO T
SELECT 10, xCol
FROM    (SELECT *
    FROM OPENROWSET (BULK 'C:\MyFile\xmlfile.xml', SINGLE_CLOB)
AS xCol) AS R(xCol);
</code>

Ejemplo 
<code sql>

SELECT CAST(
CAST (('<?xml version="1.0" encoding="iso8859-1"?>'+ vcdoc) AS VARBINARY (MAX))
AS XML);

</code>

  * [[https://docs.microsoft.com/es-es/sql/relational-databases/import-export/examples-of-bulk-import-and-export-of-xml-documents-sql-server?view=sql-server-ver15]]

=== Usando sp_xml_preparedocument ===

  * [[https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql?view=sql-server-ver15]]
<code sql>
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');
</code>
==== XML Convert =====

  * [[https://foro.elhacker.net/bases_de_datos/insertar_xml_con_dtd_interno_en_sql_server-t503432.0.html]]

da error:
<code sql>

DECLARE @doc xml
SELECT @doc = (CAST x AS xml) FROM OPENROWSET(BULK 'ruta', SINGLE_BLOB) AS T(x)

SELECT @doc = CONVERT(xml, x, 2) FROM OPENROWSET(BULK 'ruta', SINGLE_BLOB) AS T(x) 

</code>


==== Entrando a los NODOS  =====

<code sql>

DECLARE @x XML   
SET @x='<Root>  
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>  
    <row id="2"><name>moe</name></row>  
    <row id="3" />  
</Root>'  
SELECT T.c.query('.') AS result  
FROM   @x.nodes('/Root/row') T(c)  
GO  

</code>