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

Sql Date Time formats.

Fromhttp://lists.evolt.org/archive/Week-of-Mon-20020617/115646.html

without
century (yy)
with
century
(yyyy)
standard outputformat
- 0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) default
milliseconds
mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default
milliseconds
dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
20 120 - yyyy-mm-dd hh:mi:ss(24h)
21 121 - yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 - yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 130 - dd mon yyyy hh:mi:ss:mmmAM
- 131 - dd/mm/yy hh:mi:ss:mmmAM

(*) the default values (style 0 or 100, 9 or 109, and
13 or 113) always return the century (yyyy)

Calculate average daily mileage required to make yearly goal

This might be useful if you're programming a running site. For me, however, I wanted to write this calculation in SQL Server to expand my knowledge some.


--Calculates the average daily mileage needed to meet a target yearly mileage goal, based on current mileage.
CREATE FUNCTION averageDistanceNeeded (@targetMileage decimal(10,4), @currentMileage decimal(10,4))
RETURNS decimal(10,4)
AS
BEGIN
Declare @daysLeft decimal(10,4)
Declare @dt datetime

SELECT @dt = dt from openquery
(
"my-server",
'select dt = getdate()'
)
select @daysLeft = datediff(day, @dt, CAST (CAST (YEAR(@dt) AS VARCHAR(4)) + '-12-31' AS DATETIME))
return CONVERT(decimal(10,4), (@targetMileage - @currentMileage) / @daysLeft)
END
GO

--Get average daily mileage needed to reach 2000 miles, based on a current mileage
-- of 960.2
select dbo.averageDistanceNeeded(2000.0, 960.2)

Further refined version of code searching

I have now included views in the search code version. Both functions now return the object type the result was found in.


--Searches stored procedures, functions and views for code
-- containing the stringtosearch
CREATE FUNCTION findTextInCode (@StringToSearch varchar(100))
RETURNS @fNames TABLE (search varchar(100), name sysname, type char(2))
AS
BEGIN
SET @StringToSearch = '%' + @StringToSearch +'%'
INSERT @fNames
SELECT DISTINCT @stringtosearch, SO.Name, SO.Type
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
AND (SO.Type = 'P' OR SO.Type LIKE '%F%' OR SO.Type = 'V')
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
RETURN
END
GO


--Searches column names from stored procedure parameters and function,
-- user tables, and view columns
CREATE FUNCTION findTextInColumnName (@StringToSearch varchar(100))
RETURNS @fColumnNames TABLE (tablename sysname, colname sysname, type char(2))
AS
BEGIN
SET @StringToSEarch = '%' + @StringToSearch + '%'
INSERT @fColumnNames
SELECT DISTINCT SO.NAME, SC.NAME, SO.TYPE
FROM syscolumns SC (NOLOCK)
INNER JOIN
sysobjects SO (NOLOCK)
ON SC.id = SO.id
WHERE SC.NAME LIKE @StringToSearch
ORDER BY SC.Name
RETURN
END

Functions to find text in column names and in stored procedures and function definitions

Implemented in SQL Server:
This function finds a text string in a stored procedure or function definition:


CREATE FUNCTION Find_Text_In_SP_or_FN (@StringToSearch varchar(100))
RETURNS @fNames TABLE (name sysname)
AS
BEGIN
SET @StringToSearch = '%' + @StringToSearch +'%'
INSERT @fNames
SELECT DISTINCT SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.ID = SC.ID
AND (SO.Type = 'P' OR SO.Type LIKE '%F%')
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
RETURN
END
GO

This function finds a text string in a column name:


CREATE FUNCTION Find_Text_In_Column_Name (@StringToSearch varchar(100))
RETURNS @fColumnNames TABLE (colname sysname)
AS
BEGIN
SET @StringToSEarch = '%' + @StringToSearch + '%'
INSERT @fColumnNames
SELECT DISTINCT SC.NAME
FROM syscolumns SC (NOLOCK)
WHERE SC.NAME LIKE @StringToSearch
ORDER BY SC.Name
RETURN
END
GO

Some example usage for the novices:


select * from Find_Text_In_SP_or_FN('addr')
GO
Select * from Find_Text_In_Column_Name('city')
GO
Select * from Find_Text_In_Column_Name('state')
GO