Utilizacion de XML en SQL Server

Utilizando el Tipo de Datos

-- Crear una Tabla con una columna XML

CREATE TABLE XMLEjemplo (

  Col1 int NOT NULL PRIMARY KEY IDENTITY(1,1),

  Col2 varchar(20) NULL,

  Col3 xml NULL)

GO

-- Crear una tabla con una Columan xml y un valor por defecto

-- usando un CAST a XML

CREATE TABLE tblXMLExample2 (

  Col1 int NOT NULL PRIMARY KEY IDENTITY(1,1),

  Col2 varchar(20) NULL,

  Col3 xml NULL DEFAULT CAST('' AS xml))

GO

-- Declarar una variable usando el tipo de datos XML

DECLARE @doc xml

SELECT @doc = ''

GO

Asignacion Directa

DECLARE @empXML xml

SET @empXML = CAST('1234567890Perez, Juan'

    + 'Gerente de VentasVentas' AS xml)

SELECT @empXML AS DatosEmpleados

Ejecutar consulta y verificar estructura con el editor XML de SQL Server Management Studio.

Usando FOR XML

DECLARE @empXML xml

SET @empXML = (SELECT TOP 3 IdCliente,

                   Nombre,

                   Pais, Departamento

               FROM Clientes 

               FOR XML AUTO, ELEMENTS, ROOT ('Clientes'))

SELECT @empXML AS DatosClientes

Crear Un Esquema

SELECT TOP 2 IdProducto, Descripcion, Precio

FROM Productos

FOR XML AUTO, ELEMENTS, XMLSCHEMA, ROOT ('Productos')

Ejecutar consulta y verificar estructura con el editor XML de SQL Server Management Studio.

Crear un esquema a partir del esquema generado anteriormente.

CREATE XML SCHEMA COLLECTION ProductosSchema AS N''

Verificar el esquema creado:

SELECT * FROM sys.xml_schema_collections WHERE name = 'ProductosSchema'

Crear una Nueva Tabla Productos XML y asginarle el Esquema

CREATE TABLE ProductosXML (

  ProdSchemaID int NOT NULL PRIMARY KEY IDENTITY(1,1),

  XMLEmpInfo xml (ProductosSchema))

Probar Ingresar un Documento XML que no cumpla con los requisitos del Esquema, Por Ejemplo:  IdProducto de tipo varchar.

Cargando Datos usando OPENROWSET

Cargar un archivo XML usando OPENROWSET

DECLARE @prodXML xml

SET @prodXML = (SELECT *

                FROM OPENROWSET(

                    BULK 'C:\SQLServer\Bicycles.xml',  SINGLE_BLOB) AS xmlDatosProductos)

SELECT @prodXML AS DatosProductos

Insertando datos desde un archivo

1.       Crear una Tabla con una columna código de Documento,  y una columna XML para almacenar un archivo.

CREATE TABLE XMLBicicletas (

  Codigo int NOT NULL PRIMARY KEY IDENTITY(1,1),

  BiciXML xml NULL)

GO

2.       Insertar los datos en la tabla anterior desde un archivo XML

INSERT INTO XMLBicicletas(BiciXML)

SELECT *

FROM OPENROWSET(

    BULK 'C:\SQLServer\Bicycles.xml', SINGLE_BLOB) AS xmlProdData

GO

3.       Estructura del Archivo Almacenado

SELECT * FROM XMLBicicletas

<Bicycles><Product>

    <ProductID>791ProductID>

    <Name>Road-250 Red, 52Name>

    <ProductNumber>BK-R89R-52ProductNumber>

    <MakeFlag>1MakeFlag>

    <FinishedGoodsFlag>1FinishedGoodsFlag>

    <Color>RedColor>

    <SafetyStockLevel>100SafetyStockLevel>

    <ReorderPoint>75ReorderPoint>

    <StandardCost>1518.7864StandardCost>

    <ListPrice>2443.3500ListPrice>

    <Size>52Size>

    <SizeUnitMeasureCode>CM SizeUnitMeasureCode>

    <WeightUnitMeasureCode>LB WeightUnitMeasureCode>

    <Weight>15.42Weight>

    <DaysToManufacture>4DaysToManufacture>

    <ProductLine>R ProductLine>

    <Class>H Class>

    <Style>U Style>

    <ProductSubcategoryID>2ProductSubcategoryID>

    <ProductModelID>26ProductModelID>

    <SellStartDate>2002-07-01T00:00:00SellStartDate>

    <SellEndDate>2003-06-30T00:00:00SellEndDate>

    <rowguid>C9FD1DF4-9512-420A-B379-067108033B75rowguid>

    <ModifiedDate>2004-03-11T10:01:36.827ModifiedDate>

  Product> Bicycles>

Usando XQuery para retornar datos

1.       Regresar todos los nombres de los Productos.

SELECT  BiciXML.query('/Bicycles/Product/Name') AS NombreProducto

FROM XMLBicicletas

2.       Retornar el n valor de una estructura XML  XPATH

SELECT BiciXML.value('(/Bicycles/Product/ProductNumber)[5]', 'varchar(20)') AS ModeloBicicleta

FROM XMLBicicletas

3.       Retornar la cantidad de teléfonos por un Contacto

DECLARE @XML xml

SET @XML = (SELECT DocXML

            FROM XMLContactos)

SELECT @XML.query(

  '

    {

      for $contacto in /Contacts/Contact

      let $contador :=count($contacto/PhoneNumbers/PhoneNumber)

      order by $contador

      return

     

          {$contacto/Name}

          {$contador}

     

    }

  ')

4.       Retornar un Contacto Especifico

SELECT @XML.query(

  '

    {

      for $contacto in /Contacts/Contact

      let $contador :=$contacto/PhoneNumbers/PhoneNumber

      where $contacto = "Anna Palmer"

      return

     

          {$contacto/Name}

          {$contador}

     

    }

  ')