Tuesday, June 5, 2012

Reading live XML file to the SQL Server database by combining consumed and unconsumed XML data.

Task: Query the XML (LicenseKeys.xml) file from MS SQL Server database.
XML file fragment:

<?xml version="1.0" encoding="utf-8"?>
<licenseBatch xmlns="http://site/schemas/licensekey">
                <license batchID="1" batchStatus="Active" expireDays="60" admin="WESTERN_SYDNEY\rsakhamuri" adminEmail="Ramya.Sakhamuri@site.com" requester="rsakhamuri" requesterEmail="Ramya.Sakhamuri@site.com" creationTimeStamp="2010-02-04T15:46:02.525392+11:00" activationType="OfflinePossible">
                                <note>test</note>
                                <licenseKey sequence="1" key="3fea9da9-28b1-47f1-82ff-4198c96330d7" owner="ramya" ownerEmail="" status="ReActivated" lastModified="2010-02-04T05:03:40.663936Z" noOfActivation="3" maxActivationCount="50">
                                                <activator IP="127.0.0.1" userName="WESTERN_SYDNEY\rsakhamuri" email="" activationTimeStamp="2010-02-04T15:48:57.9026368+11:00">rams</activator>
                                                <activator IP="127.0.0.1" userName="WESTERN_SYDNEY\rsakhamuri" email="" activationTimeStamp="2010-02-04T15:49:11.5434364+11:00">rams</activator>
                                                <activator IP="172.24.108.48" userName="WESTERN_SYDNEY\rsakhamuri" email="ramya.sakhamuri@site.com" activationTimeStamp="2010-02-04T16:03:40.663936+11:00">ramya</activator>
                                </licenseKey>
                                <licenseKey sequence="2" key="acbebeeb-af3e-40d1-a9ca-f03dd0a5cce4" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="3" key="8efcc7c4-c367-4316-b8b3-8f5c8cfc244c" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="4" key="8b6e7919-7107-4025-bddb-5a746e1b2691" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="5" key="c5cf95d8-4b5a-458b-8704-4e8e569e2e07" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                </license>
                <license batchID="2" batchStatus="Active" expireDays="180" admin="WESTERN_SYDNEY\rsakhamuri" adminEmail="Ramya.Sakhamuri@site.com" requester="rsakhamuri" requesterEmail="Ramya.Sakhamuri@site.com" creationTimeStamp="2010-04-20T18:30:33.998494+10:00" activationType="OnlineOnly">
                                <note>for OTEN users 20-04-2010</note>
                                <licenseKey sequence="1" key="96af9492-0f01-4689-9443-2b098a75f08b" owner="Julie Esson" ownerEmail="ramya.sakhamuri@site.com" status="ReActivated" lastModified="2010-04-21T06:21:53.4190295Z" noOfActivation="2" maxActivationCount="50">
                                                <activator IP="172.24.108.190" userName="WESTERN_SYDNEY\rsakhamuri" email="ramya.sakhamuri@site.com" activationTimeStamp="2010-04-21T08:44:23.8095566+10:00">ramya sakhamuri</activator>
                                                <activator IP="172.24.111.148" userName="SYDNEY\JEsson" email="julie.esson@site.com" activationTimeStamp="2010-04-21T16:21:53.4190295+10:00">Julie Esson</activator>
                                </licenseKey>
                                <licenseKey sequence="2" key="9c5e56b7-8733-46df-9f2d-8b91a57fd805" owner="Kuan Sin" ownerEmail="kuan.sin@site.com" status="Active" lastModified="2010-04-21T06:33:50.4079932Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.107.251" userName="WESTERN_SYDNEY\ksin" email="kuan.sin@site.com" activationTimeStamp="2010-04-21T16:33:50.4079932+10:00">Kuan Sin</activator>
                                </licenseKey>
                                <licenseKey sequence="3" key="a350aff9-78c0-4446-a88e-8967771211c2" owner="Carmen Vallis" ownerEmail="carmen.vallis@site.com" status="ReActivated" lastModified="2010-07-22T23:52:25.3306929Z" noOfActivation="2" maxActivationCount="50">
                                                <activator IP="172.24.108.67" userName="WESTERN_SYDNEY\cvallis2" email="carmen.vallis@site.com" activationTimeStamp="2010-04-21T16:41:42.0516367+10:00">Carmen Vallis</activator>
                                                <activator IP="172.24.111.148" userName="WESTERN_SYDNEY\cvallis2" email="carmen.vallis1@site.com" activationTimeStamp="2010-07-23T09:52:25.3306929+10:00">Carmen Vallis</activator>
                                </licenseKey>
                                <licenseKey sequence="4" key="67865ae2-036e-4be3-a601-a55d378d7694" owner="Janet Coller" ownerEmail="janet.coller@site.com" status="Active" lastModified="2010-04-21T22:21:08.9738945Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.110.20" userName="WESTERN_SYDNEY\jcoller" email="janet.coller@site.com" activationTimeStamp="2010-04-22T08:21:08.9738945+10:00">Janet Coller</activator>
                                </licenseKey>
                                <licenseKey sequence="5" key="bd927ebb-a846-4af7-a3fe-00a827ca69fb" owner="Rob Wales" ownerEmail="robert.wales@site.com" status="Active" lastModified="2010-04-21T23:51:30.0881764Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.109.153" userName="WESTERN_SYDNEY\rwales2" email="robert.wales@site.com" activationTimeStamp="2010-04-22T09:51:30.0881764+10:00">Rob Wales</activator>
                                </licenseKey>
                </license>
</licenseBatch>
This file can be automatically imported into MS Excel and subsequently to MS SQL Server database. It can be used for solution verification.The issue with reading such XML file using OPENXML is that not all data can be extracted in one go.  The licenseKey tag may consist of multiple activator tags however OPENXML can read only one set at the time.  The paragraph "I. Specifying the xml data type in the WITH clause" of Using OPENXML describes extracting unconsumed data into OverFlow column.The solution below required enabling BULK insert for the database user and Ad Hoc Distributed Queries.  It can be achieved by running the following set of commands:sp_addsrvrolemember @loginame = N' YourDatabaseUser', @rolename = N'bulkadmin'gosp_configure 'show advanced options',1reconfigure with overridegosp_configure 'Ad Hoc Distributed Queries',1reconfigure with overridegoSolution (combines consumed and unconsumed data into the one recordset):
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop procedure dbo.udpLicenceKeys'
GOif exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udpLicenceKeys')
 and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.udpLicenceKeys
GO

PRINT 'CREATE PROC dbo.udpLicenceKeys'
GO/******************************************************************************
   NAME:              udpLicenceKeys
   PURPOSE:   REVISIONS:   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE PROCEDURE dbo.udpLicenceKeys
AS
   DECLARE  @LicenceKeys TABLE(XmlCol xml)                INSERT @LicenceKeys
                  SELECT CONVERT(xml, BulkColumn, 2) FROM
                                OPENROWSET(Bulk '\\servername\share\LicenseKeys.xml'
                                , SINGLE_BLOB) rowsetresults;                DECLARE @Data XML                SELECT @Data = XmlCol FROM @LicenceKeys                             DECLARE @idoc int
                DECLARE @doc NVARCHAR(max)
                SET @doc = (SELECT CONVERT(NVARCHAR(max),XmlCol) FROM @LicenceKeys)
                SET @doc = REPLACE(@doc,'<?xml version="1.0" encoding="utf-8"?>','')
                SET @doc = REPLACE(@doc,' xmlns="http://site.com/schemas/licensekey"','')                EXEC sp_xml_preparedocument @idoc OUTPUT, @doc                DECLARE @FindSubString VARCHAR(MAX)                SET @FindSubString = '<activator'                SELECT  T.*
                ,(CASE WHEN (LEN(convert(nvarchar(max),overflow)) -
                                                                LEN(REPLACE(convert(nvarchar(max),overflow), @FindSubString, '')))
                                                                /LEN(@FindSubString) > 0
                                                THEN REPLACE(convert(nvarchar(max),overflow)
                                                                ,'<licenseKey','<licenseKey batchID="'+CONVERT(NVARCHAR(10),BatchID)
                                                                +'" sequence="' + CONVERT(NVARCHAR(10),sequence) + '" ')
                  ELSE NULL END
                ) strXML                FROM (
                SELECT *
                FROM OPENXML (@idoc, '/licenseBatch/license/licenseKey',10)
                WITH (
                                batchID float '../@batchID'
                                ,batchStatus nvarchar(255) '../@batchStatus'
                                ,expireDays float '../@expireDays'
                                ,[admin] nvarchar(255) '../@admin'
                                ,adminEmail nvarchar(255) '../@adminEmail'
                                ,requester nvarchar(255) '../@requester'
                                ,requesterEmail nvarchar(255) '../@requesterEmail'
                                ,creationTimeStamp nvarchar(255) '../@creationTimeStamp'
                                ,activationType nvarchar(255) '../@activationType'
                                ,note nvarchar(255) '../note'
                                ,sequence float '@sequence'
                                ,[key] nvarchar(255) '@key'
                                ,[owner] nvarchar(255) '@owner'
                                ,ownerEmail nvarchar(255) '@ownerEmail'
                                ,[status] nvarchar(255) '@status'
                                ,lastModified nvarchar(255) '@lastModified'
                                ,noOfActivation float '@noOfActivation'
                                ,maxActivationCount float '@maxActivationCount'
                                ,activator nvarchar(255)
                                ,IP nvarchar(255) 'activator/@IP'
                                ,userName nvarchar(255) 'activator/@userName'
                                ,email nvarchar(255) 'activator/@email'
                                ,activationTimeStamp datetime 'activator/@activationTimeStamp'
                                ,OverFlow nvarchar(max) '@mp:xmltext')) TGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO                       
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop view dbo.udvLicenceKeys'
GOif exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvLicenceKeys')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvLicenceKeys
GOPRINT 'CREATE VIEW dbo.udvLicenceKeys'
GO/******************************************************************************
   NAME:              dbo.udvLicenceKeys
   PURPOSE:   REVISIONS:$Log$   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE VIEW dbo.udvLicenceKeys
AS
SELECT  *
FROM    OPENROWSET( 'SQLNCLI'
                    ,'Server=servername;UID=YourUserName;PWD=YourPassword;'
                    ,'SET FMTONLY OFF; SET NOCOUNT ON; Execute YourDatabaseName..udpLicenceKeys')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop function dbo.udfGet_Overflow_XML'
GOif exists (select * from sysobjects
where id = object_id(N'.dbo.udfGet_Overflow_XML')
 and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfGet_Overflow_XML
GOPRINT 'CREATE FUNCTION dbo.udfGet_Overflow_XML'
GO/******************************************************************************
   NAME:              udvGet_Overflow_XML
   PURPOSE:   REVISIONS:$Log$   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE FUNCTION dbo.udfGet_Overflow_XML()
RETURNS NVARCHAR(MAX)
AS
                BEGIN
                                DECLARE  @strXML NVARCHAR(MAX)
                                                                ,@finalXML NVARCHAR(MAX)                                DECLARE crs CURSOR
                                                                FOR select strXML from dbo.udvLicenceKeys                                SET @finalXML = '<root>'                                IF @@error = 0 BEGIN
                                                OPEN crs
                                END                                FETCH NEXT FROM crs
                                INTO   @strXML                                WHILE @@FETCH_STATUS = 0 BEGIN
                                                IF @strXML IS NOT NULL BEGIN
                                                                SET @finalXML = @finalXML + @strXML
                                                END
                                                FETCH NEXT FROM crs
                                                INTO   @strXML
                                END
                                DEALLOCATE crs
                                SET @finalXML = @finalXML + '</root>'
                                RETURN (@finalXML)
                ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop procedure dbo.udpOverflow_Recordset'
GOif exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udpOverflow_Recordset')
 and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.udpOverflow_Recordset
GO

PRINT 'CREATE PROC dbo.udpOverflow_Recordset'
GO/******************************************************************************
   NAME:              udpOverflow_Recordset
   PURPOSE:   REVISIONS:   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE PROCEDURE dbo.udpOverflow_Recordset
AS                DECLARE @DocHandle int
                DECLARE @XmlDocument nvarchar(MAX)                                SET @XmlDocument = (select dbo.udfGet_Overflow_XML())
                                EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument                                SELECT *
                                FROM OPENXML (@DocHandle, '/root/licenseKey/activator',1)
                                                  WITH (batchID float '../@batchID'
                                                                ,sequence float '../@sequence'
                                                                ,IP nvarchar(255) '@IP'
                                                                ,userName nvarchar(255) '@userName'
                                                                ,email nvarchar(255) '@email'
                                                                ,activationTimeStamp datetime '@activationTimeStamp'
                                                                ,activator nvarchar(255) '.')               
                                EXEC sp_xml_removedocument @DocHandleGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop view dbo.udvOverflow_Recordset'
GOif exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvOverflow_Recordset')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvOverflow_Recordset
GOPRINT 'CREATE VIEW dbo.udvOverflow_Recordset'
GO/******************************************************************************
   NAME:              dbo.udvOverflow_Recordset
   PURPOSE:   REVISIONS:$Log$   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE VIEW dbo.udvOverflow_Recordset
AS
SELECT  *
FROM    OPENROWSET( 'SQLNCLI'
                                                                                ,'Server=servername;UID=YourUserName;PWD=YourPass;'
                    ,'SET FMTONLY OFF; SET NOCOUNT ON; Execute DatabaseName..udpOverflow_Recordset')

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop view dbo.udvOverflow_Full_Recordset'
GOif exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvOverflow_Full_Recordset')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvOverflow_Full_Recordset
GOPRINT 'CREATE VIEW dbo.udvOverflow_Full_Recordset'
GO/******************************************************************************
   NAME:              dbo.udvOverflow_Full_Recordset
   PURPOSE:   REVISIONS:$Log$   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE VIEW dbo.udvOverflow_Full_Recordset
AS
                SELECT DISTINCT
                                                O.batchID
                                                ,L.batchStatus
                                                ,L.expireDays
                                                ,L.admin
                                                ,L.adminEmail
                                                ,L.requester
                                                ,L.requesterEmail
                                                ,L.creationTimeStamp
                                                ,L.activationType
                                                ,L.note
                                                ,L.sequence
                                                ,L.[key]
                                                ,L.owner
                                                ,L.ownerEmail
                                                ,L.status
                                                ,L.lastModified
                                                ,L.noOfActivation
                                                ,L.maxActivationCount
                                                ,O.activator
                                                ,O.IP
                                                ,O.userName
                                                ,O.email
                                                ,O.activationTimeStamp
                                                 from dbo.udvLicenceKeys L
                INNER JOIN dbo.udvOverflow_Recordset O
                 ON L.batchID = O.batchID
                 WHERE L.sequence = O.sequenceGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOPRINT 'drop function dbo.udfLicenceKeys'
GOif exists (select * from sysobjects
where id = object_id(N'.dbo.udfLicenceKeys')
 and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfLicenceKeys
GOPRINT 'CREATE FUNCTION dbo.udfLicenceKeys'
GO/******************************************************************************
   NAME:              udfLicenceKeys
   PURPOSE:   REVISIONS:$Log$   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE FUNCTION dbo.udfLicenceKeys()
RETURNS @tempTBL TABLE (
                                                                 batchID float
                                                                ,batchStatus nvarchar(255)
                                                                ,expireDays float
                                                                ,[admin] nvarchar(255)
                                                                ,adminEmail nvarchar(255)
                                                                ,requester nvarchar(255)
                                                                ,requesterEmail nvarchar(255)
                                                                ,creationTimeStamp nvarchar(255)
                                                                ,activationType nvarchar(255)
                                                                ,note nvarchar(255)
                                                                ,sequence float
                                                                ,[key] nvarchar(255)
                                                                ,[owner] nvarchar(255)
                                                                ,ownerEmail nvarchar(255)
                                                                ,[status] nvarchar(255)
                                                                ,lastModified nvarchar(255)
                                                                ,noOfActivation float
                                                                ,maxActivationCount float
                                                                ,activator nvarchar(255)
                                                                ,IP nvarchar(255)
                                                                ,userName nvarchar(255)
                                                                ,email nvarchar(255)
                                                                ,activationTimeStamp datetime)
AS
                BEGIN
                                INSERT INTO @tempTBL
                                SELECT batchID
                                                ,batchStatus
                                                ,expireDays
                                                ,admin
                                                ,adminEmail
                                                ,requester
                                                ,requesterEmail
                                                ,creationTimeStamp
                                                ,activationType
                                                ,note
                                                ,sequence
                                                ,[key]
                                                ,owner
                                                ,ownerEmail
                                                ,status
                                                ,lastModified
                                                ,noOfActivation
                                                ,maxActivationCount
                                                ,activator
                                                ,IP
                                                ,userName
                                                ,email
                                                ,activationTimeStamp
                                FROM  dbo.udvLicenceKeys                                INSERT INTO @tempTBL
                                SELECT batchID
                                                ,batchStatus
                                                ,expireDays
                                                ,admin
                                                ,adminEmail
                                                ,requester
                                                ,requesterEmail
                                                ,creationTimeStamp
                                                ,activationType
                                                ,note
                                                ,sequence
                                                ,[key]
                                                ,owner
                                                ,ownerEmail
                                                ,status
                                                ,lastModified
                                                ,noOfActivation
                                                ,maxActivationCount
                                                ,activator
                                                ,IP
                                                ,userName
                                                ,email
                                                ,activationTimeStamp
                                FROM   dbo.udvOverflow_Full_Recordset
                                RETURN
                ENDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 The final query:SELECT * FROM dbo.udfLicenceKeys()
 
If you find this script useful please donate generously.

No comments:

Post a Comment