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