DECLARE @x xml
CREATE TABLE WomenResults
(
FirstName varchar(100),
LastName varchar(100),
AgeGroup varchar(100) ,
CompNumber int,
event varchar(100),
avgscore float,
bscore float
)
SELECT @x=P
FROM OPENROWSET (BULK 'C:/proscore5/3WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
INSERT INTO WomenResults
SELECT*
FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1)
WITH(
FirstName varchar(100) '../../@FirstName',
LastName varchar(100) '../../@LastName',
AgeGroup varchar(100) '../../@AG',
CompNumber int '../../@CompNum',
event varchar(100),
avgscore float,
bscore float)
ORDER BY AgeGroup DESC
,event ASC
,avgscore DESC
Then you will need to deal with updates, inserts, deletes (do it to another table, then you aren't reading the xml more than once).