I don’t currently have a work-based use for 2005, so I thought I’d use the only readily available collection of data that I have at my disposal… my running log. This is an export file from RunningAhead.com. I don’t think I’m completely loading the XML data yet, and I don’t have database tables to load to yet, either. However, this is my implementation of some OPENXML examples.
DECLARE @hdoc int
DECLARE @xmlDoc XML
SET @xmlDoc = (
SELECT * FROM OPENROWSET (
BULK ‘C:projectsrunningaheadlog.1.321C02log.xml’, SINGLE_CLOB
) AS xmlData
)
SELECT @xmlDoc
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc
SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/CourseCollection/Course’, 2)
WITH
(
ID varchar(32),
Name varchar(255) ,
Surface varchar(255),
City varchar(255),
State char(3),
Notes varchar(255)
)
SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/EquipmentCollection/Shoe’, 2)
WITH
(
ID varchar(32),
Make varchar(255) ,
Model varchar(255),
Serial char(20),
InitDistance decimal(7,2),
PurchaseDate datetime,
Retired char(3),
Size char(10),
SizeSystem char(40)
)
SELECT * FROM
OPENXML(@hdoc, ‘/RunningAHEADLog/EventCollection/Run’, 2)
WITH
(
ID char(32),
Date datetime,
Time datetime,
Type char(15),
Weight decimal(4,1),
WeightUnit char(12),
AvgHR tinyint,
MaxHR tinyint,
Quality tinyint,
Effort tinyint,
CourseID char(32),
Distance decimal(8,2),
DistanceUnit char(12),
Duration char(20)
)
EXEC sp_xml_removedocument @hdoc