TheFirstOne - Tuesday, May 1, 2018 4:57 PM

This is my 4th day on SQL so I am a newbie. I created a query to import from an xml file. It works but I am having trouble dumping it to a table. Everywhere I look examples are adding the data in the query and not from the query results (memory). Here is basically what I am trying to do.

• Import from an xml file every 5 seconds
• dump the results to the database/table
• the database/table is updated every 5 sec
• Each age group is output to a separate JSON/xml file.

Code  I have to  import file.
DECLARE @x xml

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

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

Sample of my data. Each xml file has about 50 to 80 records.

<?xml version="1.0" encoding="ISO-8859-1"?>
<MeetResultsMsg Type="ARTW3" EventID="" Session="1" >
 <AthleteResults>
  <Athlete Type="ARTW3" CompNum="333" LastName="Mockford" FirstName="Holly" AG="SRA">
   <Scores>
    <Score event="1" avgscore="9.200" bscore="9.200">
     <JudgeScore id="1" score="9.200"/>
     <JudgeScore id="2" score="9.200"/>
    </Score>
    <Score event="2" avgscore="9.250" bscore="9.250">
     <JudgeScore id="1" score="9.250"/>
     <JudgeScore id="2" score="9.250"/>
    </Score>
    <Score event="3" avgscore="9.375" bscore="9.375">
     <JudgeScore id="1" score="9.450"/>
     <JudgeScore id="2" score="9.300"/>
    </Score>
    <Score event="4" avgscore="9.500" bscore="9.500">
     <JudgeScore id="1" score="9.600"/>
     <JudgeScore id="2" score="9.400"/>
    </Score>
    <Score event="AA" avgscore="37.325"/>
   </Scores>
   <Places>
    <Place event="AA" rank="1" order="1"/>
    <Place event="1" rank="13" order="13"/>
    <Place event="2" rank="8" order="8"/>
    <Place event="3" rank="1" order="1"/>
    <Place event="4" rank="1" order="1"/>
   </Places>
  </Athlete>
 </AthleteResults>
</MeetResultsMsg>

So basically this is for scoring gymnastics meets. We have software that spits out the xml and updates it every 5 sec. We need to import the data and break it up by Age Groups(AG). I am figuring it out step by step but I am stuck getting it into a database/table. Any help would be appreciated.

Tim