Importing a runningahead log.xml into SQL Server 2005 using OPENXML


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

,

Leave a Reply

%d bloggers like this: