· 10 years ago · Aug 31, 2016, 06:28 AM
1<?xml version="1.0" encoding="utf-8"?>
2<badges>
3 <row Id="1" UserId="2" Name="Autobiographer" Date="2011-01-19T20:52:02.027" Class="3" TagBased="False" />
4 <row Id="2" UserId="4" Name="Autobiographer" Date="2011-01-19T20:57:02.100" Class="3" TagBased="False" />
5 <row Id="3" UserId="6" Name="Autobiographer" Date="2011-01-19T20:57:02.133" Class="3" TagBased="False" />
6 ...
7 <row Id="176685" UserId="99330" Name="Supporter" Date="2016-03-06T03:34:14.827" Class="3" TagBased="False" />
8</badges>
9
10CREATE TABLE RawDataXml.Badges (
11 SiteId UNIQUEIDENTIFIER PRIMARY KEY,
12 ApiSiteParameter NVARCHAR(256) NOT NULL,
13 RawDataXml XML NULL,
14 XmlDataSize BIGINT NULL,
15 Inserted DATETIME2 DEFAULT GETDATE(),
16 CONSTRAINT fk_Badges_SiteId FOREIGN KEY (SiteId) REFERENCES CleanData.Sites(Id)
17);
18CREATE TABLE CleanData.Badges (
19 SiteId UNIQUEIDENTIFIER NOT NULL,
20 ApiSiteParameter NVARCHAR(256) NOT NULL,
21 RowId INT,
22 UserId INT,
23 Name NVARCHAR(256),
24 CreationDate DATETIME2,
25 Class INT,
26 TagBased BIT,
27 Inserted DATETIME2 DEFAULT GETDATE(),
28 CONSTRAINT fk_Badges_SiteId FOREIGN KEY (SiteId) REFERENCES CleanData.Sites(Id)
29);
30CREATE TABLE RawDataXml.Globals (
31 Parameter NVARCHAR(256) NOT NULL,
32 Value NVARCHAR(256) NOT NULL,
33 Inserted DATETIME2 DEFAULT GETDATE()
34);
35
36Parameter Value
37SourcePath D:Downloadsstackexchange
38TargetSite codereview.stackexchange.com
39TargetSite meta.codereview.stackexchange.com
40TargetSite stats.stackexchange.com
41TargetSite meta.stats.stackexchange.com
42
43IF EXISTS (
44 SELECT 1
45 FROM INFORMATION_SCHEMA.ROUTINES
46 WHERE SPECIFIC_SCHEMA = 'RawDataXml'
47 AND SPECIFIC_NAME = 'usp_LoadBadgesXml'
48)
49DROP PROCEDURE RawDataXml.usp_LoadBadgesXml;
50GO
51
52CREATE PROCEDURE RawDataXml.usp_LoadBadgesXml
53 @SiteDirectory NVARCHAR(256),
54 -- Delete the loaded XML file after processing if True/1 (default True):
55 @DeleteXmlRawDataAfterProcessing BIT = 1,
56 -- Display/Return results to caller if @ReturnRows is set to True (default False)
57 @ReturnRows BIT = 0
58AS
59BEGIN
60 SET NOCOUNT ON;
61 -- Fetch global source path parameter:
62 DECLARE @SourcePath NVARCHAR(256);
63 DECLARE @bslash CHAR = CHAR(92);
64 SET @SourcePath = (SELECT Value FROM RawDataXml.Globals WHERE Parameter = 'SourcePath');
65 -- Make sure path ends with backslash (ASCII char 92)
66 IF(SELECT RIGHT(@SourcePath, 1)) <> @bslash SET @SourcePath += @bslash;
67
68 -- Fetch site identifiers based on @SiteDirectory parameter:
69 DECLARE @SiteId UNIQUEIDENTIFIER;
70 DECLARE @ApiSiteParameter NVARCHAR(256);
71 SELECT
72 @SiteId = Id,
73 @ApiSiteParameter = ApiSiteParameter
74 FROM CleanData.Sites
75 WHERE SiteDirectory = @SiteDirectory;
76
77 -- Throw error if @SiteDirectory parameter does not match an existing site:
78 IF @SiteId IS NULL OR @ApiSiteParameter IS NULL
79 BEGIN
80 DECLARE @ErrMsg NVARCHAR(512) = 'The input site directory "' + @SiteDirectory + '" could not be matched to an existing site. Please verify and try again.';
81 RAISERROR(@ErrMsg, 11, 1);
82 END
83
84 -- Delete any previous XML data that may be present for the site:
85 DELETE FROM RawDataXml.Badges
86 WHERE SiteId = @SiteId;
87
88 /** XML FILE HANDLING **
89 This section loads the XML file from the file system into a table.
90 If @DeleteXmlRawDataAfterProcessing is set to 1 (default)
91 this XML data will be deleted from the database (but not from the file system)
92 after the data is parsed into a relational table (below).
93 *****/
94
95 DECLARE @FilePath NVARCHAR(512) = @SourcePath + @SiteDirectory + @bslash + 'Badges.xml';
96 DECLARE @SQL_OPENROWSET_QUERY NVARCHAR(1024);
97
98 -- Dynamic SQL is used here because OPENROWSET will only accept a string literal as argument for the file path.
99 SET @SQL_OPENROWSET_QUERY =
100 'INSERT INTO RawDataXml.Badges (SiteId, ApiSiteParameter, RawDataXml)' + CHAR(10)
101 + 'SELECT ' + QUOTENAME(@SiteId, '''') + ', ' + CHAR(10)
102 + QUOTENAME(@ApiSiteParameter, '''') + ', ' + CHAR(10)
103 + 'CONVERT(XML, BulkColumn) AS BulkColumn' + CHAR(10)
104 + 'FROM OPENROWSET(BULK ' + QUOTENAME(@FilePath, '''') + ', SINGLE_BLOB) AS x;'
105
106 PRINT CONVERT(NVARCHAR(256), GETDATE(), 21) + ' Processing ' + @FilePath;
107
108 -- Execute the dynamic query to load XML into the table:
109 EXECUTE sp_executesql @SQL_OPENROWSET_QUERY;
110
111 /** XML DATA PARSING & PROCESSING **
112 This section parses the loaded XML document into columns and puts those in CleanData.Badges table.
113 If previous data existed, that data is deleted prior to adding new data, to avoid duplication of rows
114 and ensure a "fresh" set of data.
115 *****/
116
117 -- Clear any existing data:
118 DELETE FROM CleanData.Badges
119 WHERE SiteId = @SiteId;
120
121 -- Prepare XML document for parsing:
122 DECLARE @XML AS XML;
123 DECLARE @Doc AS INT;
124 SELECT @XML = RawDataXml
125 FROM RawDataXml.Badges
126 WHERE SiteId = @SiteId;
127 EXEC sp_xml_preparedocument @Doc OUTPUT, @XML;
128
129 -- Parse XML <row> node attributes and insert them into their respective columns:
130 INSERT INTO CleanData.Badges (
131 SiteId,
132 ApiSiteParameter,
133 RowId,
134 UserId,
135 Name,
136 CreationDate,
137 Class,
138 TagBased
139 )
140 SELECT
141 @SiteId,
142 @ApiSiteParameter,
143 Id,
144 UserId,
145 Name,
146 [Date],
147 Class,
148 CASE
149 WHEN LOWER(TagBased) = 'true' THEN 1
150 ELSE 0
151 END AS TagBased
152 FROM OPENXML(@Doc, 'badges/row')
153 WITH (
154 Id INT '@Id',
155 UserId INT '@UserId',
156 Name NVARCHAR(256) '@Name',
157 [Date] DATETIME2 '@Date',
158 Class INT '@Class',
159 TagBased NVARCHAR(256) '@TagBased'
160 );
161
162 EXEC sp_xml_removedocument @Doc;
163
164 -- Delete the loaded XML file after processing if True/1 (default True):
165 IF @DeleteXmlRawDataAfterProcessing = 1
166 BEGIN
167 DELETE FROM RawDataXml.Badges
168 WHERE SiteId = @SiteId;
169 END
170
171 -- Display/Return results to caller if @ReturnRows is set to True (default False)
172 IF @ReturnRows = 1
173 BEGIN
174 SELECT * FROM CleanData.Badges
175 WHERE SiteId = @SiteId
176 ORDER BY CreationDate ASC;
177 END
178END
179GO
180
181DECLARE @Start DATETIME2 = GETDATE();
182DECLARE @RowsProcessed INT;
183DECLARE @Now DATETIME2;
184
185DECLARE @CurrentSite NVARCHAR(256);
186DECLARE _SitesToProcess CURSOR FOR
187 SELECT Value
188 FROM RawDataXml.Globals
189 WHERE Parameter = 'TargetSite';
190OPEN _SitesToProcess;
191FETCH NEXT FROM _SitesToProcess INTO @CurrentSite;
192
193WHILE @@FETCH_STATUS = 0
194BEGIN
195 SET @Now = GETDATE();
196 EXECUTE RawDataXml.usp_LoadBadgesXml @CurrentSite;
197 PRINT 'Processing time: ' + CAST(DATEDIFF(MILLISECOND, @Now, GETDATE()) AS VARCHAR(20)) +' ms.';
198 FETCH NEXT FROM _SitesToProcess INTO @CurrentSite;
199END
200
201CLOSE _SitesToProcess;
202DEALLOCATE _SitesToProcess;
203
204PRINT 'TOTAL Processing time: ' + CAST(DATEDIFF(MILLISECOND, @Start, GETDATE()) AS VARCHAR(20)) +' ms.';
205
206SELECT * FROM CleanData.Badges ORDER BY CreationDate DESC;
207
2082016-08-31 00:05:04.983 Processing D:Downloadsstackexchangecodereview.stackexchange.comBadges.xml
209Processing time: 8060 ms.
2102016-08-31 00:05:13.033 Processing D:Downloadsstackexchangemeta.codereview.stackexchange.comBadges.xml
211Processing time: 1517 ms.
2122016-08-31 00:05:14.550 Processing D:Downloadsstackexchangestats.stackexchange.comBadges.xml
213Processing time: 8120 ms.
2142016-08-31 00:05:22.670 Processing D:Downloadsstackexchangemeta.stats.stackexchange.comBadges.xml
215Processing time: 1740 ms.
216TOTAL Processing time: 19437 ms.
217
218(345368 row(s) affected)