Importing from XML to SQL 2014

Question

Importing from XML to SQL 2014

Viewing 15 posts - 16 through 30 (of 128 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99143 More actions · Answer 1

Thanks Steve, that's much more what I was getting up with the table design.

TheFirstOne, please try to put your code in code BBcode markup; it's makes your code much harder to read if it isn't inside them (see my signature). Have a look at Steve's DDL for the tables, and see how well you can get that to work for your needs. if you're not sure how to then import your xml data, we can then move onto that step next.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

TheFirstOne SSCommitted Points: 1659 More actions · Answer 2

Thom A - Tuesday, May 8, 2018 1:56 AM
Thanks Steve, that's much more what I was getting up with the table design.
TheFirstOne, please try to put your code in code BBcode markup; it's makes your code much harder to read if it isn't inside them (see my signature). Have a look at Steve's DDL for the tables, and see how well you can get that to work for your needs. if you're not sure how to then import your xml data, we can then move onto that step next.

Let mr get my head wraped on the updated code as it is getting more complex. I regtet not taking Sql 20 years ago.ðŸ˜

TheFirstOne SSCommitted Points: 1659 More actions · Answer 3

TheFirstOne - Tuesday, May 8, 2018 10:35 AM
Thom A - Tuesday, May 8, 2018 1:56 AM
Thanks Steve, that's much more what I was getting up with the table design.
TheFirstOne, please try to put your code in code BBcode markup; it's makes your code much harder to read if it isn't inside them (see my signature). Have a look at Steve's DDL for the tables, and see how well you can get that to work for your needs. if you're not sure how to then import your xml data, we can then move onto that step next.
Let mr get my head wraped on the updated code as it is getting more complex. I regtet not taking Sql 20 years ago.ðŸ˜

I just wanted it say if it takes me few days to respond on changes as I am still working on the project, I just have a few things coming up. I need to get the sailboat ready for summer, do some camping and stuff. I really appreciate the help and will keep up on it as much as possible.
Tim

TheFirstOne SSCommitted Points: 1659 More actions · Answer 4

I am back for a while. Thank you sgmunson for the example. I have modified it to reflect all the data I need to import whether needed or not. I do like importing all the data as it may be needed in future revision and it will already be coded. Here is what I have. I am getting a Foreign key 'FK__Venues__VenueAdd__3259AED6' references invalid table for every table after dbo.Addresses which I am assuming is that I don't have any data yet. I did add one line of data to the address table but still got the error. I am assuming I need to import the xml data after each table creation? Also I added the USAGNum int, & CompNum int, in the dbo.Competitiors table but do not know if I need any more code after the int.

USE meetscoreslive

--===== If the Addresses table already exists, Drop Table IF OBJECT_ID('dbo.Addresses','U') IS NOT NULL DROP TABLE dbo.Addresses CREATE TABLE dbo.Addresses ( AddressID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, AddressType char(1) NOT NULL, -- 'C' = Competitor, 'V' = Venue AddressLine1 varchar(50) NOT NULL, AddressLine2 varchar(50), AddressLine3 varchar(50), City varchar(30) NOT NULL, StateAbbr char(2), PostalCode varchar(10), Country varchar(30) ); GO

--===== If the Venues table already exists, Drop Table IF OBJECT_ID('dbo.Venues','U') IS NOT NULL DROP TABLE dbo.Venues

CREATE TABLE dbo.Venues ( VenueID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, VenueDescription varchar(50) NOT NULL, VenueAddressID int NOT NULL REFERENCES dbo.Addresses.AddressID ); GO

--===== If the EventList table already exists, Drop Table IF OBJECT_ID('dbo.EventList','U') IS NOT NULL DROP TABLE dbo.EventList

CREATE TABLE dbo.EventList ( EventID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, EventName varchar(50) NOT NULL, EventDT datetime, EventDate AS CONVERT(date, EventDT) PERSISTED, EventTime AS CONVERT(time, EventDT) PERSISTED, EventDuration smallint, -- Number of minutes VenueID int REFERENCES Venues.VenueID ); GO

--===== If the Competitors table already exists, Drop Table IF OBJECT_ID('dbo.Competitors','U') IS NOT NULL DROP TABLE dbo.Competitors

CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, FirstName varchar(20) NOT NULL, LastName varchar(20) NOT NULL, FullName AS LastName + ', ' + FirstName PERSISTED NOT NULL, AthleteType varchar(20) NOT NULL, EventSession int, AthleteLevel varchar(20), USAGNum int CompNum int, AthleteClubname varchar(50) NOT NULL, CompetitorAddressID int REFERENCES dbo.Addresses.AddressID, Gender char(1) NOT NULL, CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')), PhoneNumber varchar(10), AlternatePhone varchar(10), EMailAddress varchar(128) ); GO

--===== If the ParticipantList table already exists, Drop Table IF OBJECT_ID('dbo.ParticipantList','U') IS NOT NULL DROP TABLE dbo.ParticipantList

CREATE TABLE dbo.ParticipantList ( EventID int NOT NULL REFERENCES EventList.EventID, CompetitorID int NOT NULL REFERENCES Competitors.CompetitorID, SesionNumber int NOT NULL REFERENCES dbo.Competitors.EventSession, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SesionNumber ASC ) ); GO

Sue_H SSC Guru Points: 90860 More actions · Answer 5

TheFirstOne - Tuesday, May 15, 2018 12:45 PM
I am back for a while. Thank you sgmunson for the example. I have modified it to reflect all the data I need to import whether needed or not. I do like importing all the data as it may be needed in future revision and it will already be coded. Here is what I have. I am getting a Foreign key 'FK__Venues__VenueAdd__3259AED6' references invalid table for every table after dbo.Addresses which I am assuming is that I don't have any data yet. I did add one line of data to the address table but still got the error. I am assuming I need to import the xml data after each table creation? Also I added the USAGNum int, & CompNum int, in the dbo.Competitiors table but do not know if I need any more code after the int.
USE meetscoreslive
--===== If the Addresses table already exists, Drop Table IF OBJECT_ID('dbo.Addresses','U') IS NOT NULL         DROP TABLE dbo.Addresses    CREATE TABLE dbo.Addresses (     AddressID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,     AddressType char(1) NOT NULL,   -- 'C' = Competitor, 'V' = Venue     AddressLine1 varchar(50) NOT NULL,     AddressLine2 varchar(50), AddressLine3 varchar(50),     City varchar(30) NOT NULL,     StateAbbr char(2),     PostalCode varchar(10),     Country varchar(30) ); GO
--===== If the Venues table already exists, Drop Table IF OBJECT_ID('dbo.Venues','U') IS NOT NULL         DROP TABLE dbo.Venues
CREATE TABLE dbo.Venues (     VenueID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,     VenueDescription varchar(50) NOT NULL,     VenueAddressID int NOT NULL REFERENCES dbo.Addresses.AddressID ); GO
--===== If the EventList table already exists, Drop Table IF OBJECT_ID('dbo.EventList','U') IS NOT NULL         DROP TABLE dbo.EventList
CREATE TABLE dbo.EventList (     EventID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, EventName varchar(50) NOT NULL, EventDT datetime, EventDate AS CONVERT(date, EventDT) PERSISTED,     EventTime AS CONVERT(time, EventDT) PERSISTED, EventDuration smallint,          -- Number of minutes     VenueID int REFERENCES Venues.VenueID ); GO
--===== If the Competitors table already exists, Drop Table IF OBJECT_ID('dbo.Competitors','U') IS NOT NULL         DROP TABLE dbo.Competitors
CREATE TABLE dbo.Competitors (     CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,     FirstName varchar(20) NOT NULL,     LastName varchar(20) NOT NULL, FullName AS LastName + ', ' + FirstName PERSISTED NOT NULL, AthleteType varchar(20) NOT NULL,     EventSession int,     AthleteLevel varchar(20),     USAGNum int, --===== Is this correct?    CompNum int, --===== Is this correct?    AthleteClubname varchar(50) NOT NULL,     CompetitorAddressID int REFERENCES dbo.Addresses.AddressID,         Gender char(1) NOT NULL, CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')),     PhoneNumber varchar(10), AlternatePhone varchar(10), EMailAddress varchar(128) ); GO
--===== If the ParticipantList table already exists, Drop Table IF OBJECT_ID('dbo.ParticipantList','U') IS NOT NULL         DROP TABLE dbo.ParticipantList
CREATE TABLE dbo.ParticipantList (     EventID int NOT NULL REFERENCES EventList.EventID, CompetitorID int NOT NULL REFERENCES Competitors.CompetitorID,     SesionNumber int NOT NULL REFERENCES dbo.Competitors.EventSession, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED    (    EventID ASC,      CompetitorID ASC,      SesionNumber ASC    ) ); GO

For the foreign keys I don't think it's the data causing the problems. Try referencing your foreign keys with
schema.table(Column). So on the venues, try using:
VenueAddressID int NOT NULL REFERENCES dbo.Addresses(AddressID)

Sue

TheFirstOne SSCommitted Points: 1659 More actions · Answer 6

That worked. Here I am creating 4 tables.

USE meetscoreslive

--===== If the Competitors table already exists, Drop Table IF OBJECT_ID('dbo.Competitors','U') IS NOT NULL DROP TABLE dbo.Competitors

CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20) NOT NULL, FirstName varchar(20) NOT NULL, LastName varchar(20) NOT NULL, FullName AS LastName + ', ' + FirstName PERSISTED NOT NULL, AthleteType varchar(20) NOT NULL, AthleteLevel varchar(20), EventSession int, USAGNum int, CompNum int, AthleteClubname varchar(50) NOT NULL, Gender char(1) NOT NULL, CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO

--===== If the Addresses table already exists, Drop Table IF OBJECT_ID('dbo.Addresses','U') IS NOT NULL DROP TABLE dbo.AddressesCREATE TABLE dbo.Addresses ( AddressID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, AddressType char(1) NOT NULL, -- 'C' = Competitor, 'V' = Venue AddressLine1 varchar(50) NOT NULL, AddressLine2 varchar(50), AddressLine3 varchar(50), City varchar(30) NOT NULL, StateAbbr char(2), PostalCode varchar(10), Country varchar(30) ); GO

--===== If the EventList table already exists, Drop Table IF OBJECT_ID('dbo.EventList','U') IS NOT NULL DROP TABLE dbo.EventList

CREATE TABLE dbo.EventList ( EventID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, EventName varchar(50) NOT NULL, EventDT datetime, EventDate AS CONVERT(date, EventDT) PERSISTED, EventTime AS CONVERT(time, EventDT) PERSISTED, EventDuration smallint, -- Number of minutes Venue varchar(50) ); GO

--===== If the ParticipantList table already exists, Drop Table IF OBJECT_ID('dbo.ParticipantList','U') IS NOT NULL DROP TABLE dbo.ParticipantList

CREATE TABLE dbo.ParticipantList ( EventID int NOT NULL REFERENCES dbo.EventList(EventID), CompetitorID int NOT NULL REFERENCES dbo.Competitors(CompetitorID), CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC ) ); GO

Now should I import the data in each table in order or does it not matter? I am getting this error when trying to drop tables. If I delete them in order from last created to first it works. How do I drop a table even though it has a reference key?

TheFirstOne SSCommitted Points: 1659 More actions · Answer 7

So I have a couple of dilemmas. I can create a good table for dbo.Competitors.

CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20), FirstName varchar(20), LastName varchar(20), FullName AS LastName + ', ' + FirstName PERSISTED, Level varchar(20), AgeGroup varchar(20), Session int, USAGNum int, CompNum int, ClubName varchar(50), Gender char(1), CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO

but I need to create a dbo.participant table that imports from an additional xml file and gets data from the dbo.competitors table also. Event, AvgScore & Bscore

CREATE TABLE dbo.ParticipantList ( EventID int REFERENCES dbo.EventList(EventID), CompetitorID int REFERENCES dbo.Competitors(CompetitorID), SessionID int, Type varchar(20), AgeGroup varchar(20), CompNum int, event varchar(10), avgscore float, bscore float, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SessionID ASC ) ); GO

My other issue is I have 14 xml files that have all the data. The names stay the same. They are the following...2-10/xb/xs/xg/xp/xd(WomenResultsDay1). Not all files have data as the scoring software only outputs selected sessions and levels. So I either need to import all possible files and have NULL values and then select the session and levels for outputs or select the session and levels by a check box and then import the data? It seams to be getting complicated.

DECLARE @x xml

SELECT @x=P FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

INSERT INTO dbo.ParticipantList SELECT*

FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1) WITH(

event varchar(10), avgscore float, bscore float);

sgmunson SSC Guru Points: 110651 More actions · Answer 8

TheFirstOne - Tuesday, May 15, 2018 4:09 PM
So I have a couple of dilemmas. I can create a good table for dbo.Competitors.
CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20), FirstName varchar(20), LastName varchar(20), FullName AS LastName + ', ' + FirstName PERSISTED, Level varchar(20), AgeGroup varchar(20), Session int, USAGNum int, CompNum int, ClubName varchar(50), Gender char(1), CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO
but I need to create a dbo.participant table that imports from an additional xml file and gets data from the dbo.competitors table also. Event, AvgScore & Bscore
CREATE TABLE dbo.ParticipantList ( EventID int REFERENCES dbo.EventList(EventID), CompetitorID int REFERENCES dbo.Competitors(CompetitorID), SessionID int, Type varchar(20), AgeGroup varchar(20), CompNum int, event varchar(10), avgscore float, bscore float, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SessionID ASC ) ); GO
My other issue is I have 14 xml files that have all the data. The names stay the same. They are the following...2-10/xb/xs/xg/xp/xd(WomenResultsDay1). Not all files have data as the scoring software only outputs selected sessions and levels. So I either need to import all possible files and have NULL values and then select the session and levels for outputs or select the session and levels by a check box and then import the data? It seams to be getting complicated.
DECLARE @x xml
SELECT @x=P FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
INSERT INTO dbo.ParticipantList SELECT*
FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1) WITH(
event varchar(10), avgscore float, bscore float);

As the data in the XML files is quite likely NOT normalized, adapting to normalized table structures will require some additional handling, but should be well worth it in the long run. You can always import the XML files to separate staging tables that just take the data "as is", and then you can "transform" the data into normalized form with queries that you create for that purpose. As to the foreign key difficulties you ran into, that's kind of the way it's supposed to work. You wouldn't want to drop a table that some other table is dependent on, at least most of the time, so yes, for testing purposes, you always want to do it in reverse order from how the tables were created. Foreign key relationships help keep bad data out of your database. That's why you would need to load the address data first, which then supports the other tables.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TheFirstOne SSCommitted Points: 1659 More actions · Answer 9

sgmunson - Wednesday, May 16, 2018 6:52 AM
TheFirstOne - Tuesday, May 15, 2018 4:09 PM
So I have a couple of dilemmas. I can create a good table for dbo.Competitors.
CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20), FirstName varchar(20), LastName varchar(20), FullName AS LastName + ', ' + FirstName PERSISTED, Level varchar(20), AgeGroup varchar(20), Session int, USAGNum int, CompNum int, ClubName varchar(50), Gender char(1), CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO
but I need to create a dbo.participant table that imports from an additional xml file and gets data from the dbo.competitors table also. Event, AvgScore & Bscore
CREATE TABLE dbo.ParticipantList ( EventID int REFERENCES dbo.EventList(EventID), CompetitorID int REFERENCES dbo.Competitors(CompetitorID), SessionID int, Type varchar(20), AgeGroup varchar(20), CompNum int, event varchar(10), avgscore float, bscore float, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SessionID ASC ) ); GO
My other issue is I have 14 xml files that have all the data. The names stay the same. They are the following...2-10/xb/xs/xg/xp/xd(WomenResultsDay1). Not all files have data as the scoring software only outputs selected sessions and levels. So I either need to import all possible files and have NULL values and then select the session and levels for outputs or select the session and levels by a check box and then import the data? It seams to be getting complicated.
DECLARE @x xml
SELECT @x=P FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
INSERT INTO dbo.ParticipantList SELECT*
FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1) WITH(
event varchar(10), avgscore float, bscore float);
As the data in the XML files is quite likely NOT normalized, adapting to normalized table structures will require some additional handling, but should be well worth it in the long run. You can always import the XML files to separate staging tables that just take the data "as is", and then you can "transform" the data into normalized form with queries that you create for that purpose. As to the foreign key difficulties you ran into, that's kind of the way it's supposed to work. You wouldn't want to drop a table that some other table is dependent on, at least most of the time, so yes, for testing purposes, you always want to do it in reverse order from how the tables were created. Foreign key relationships help keep bad data out of your database. That's why you would need to load the address data first, which then supports the other tables.

So since I will be restarting the program for every new session (up to 10 over 3 days) should I not drop the tables and instead erase the data? There are changes to the athletes right up to start time so I need to have the most up to date data for each session.

TheFirstOne SSCommitted Points: 1659 More actions · Answer 10

TheFirstOne - Wednesday, May 16, 2018 12:27 PM
sgmunson - Wednesday, May 16, 2018 6:52 AM
TheFirstOne - Tuesday, May 15, 2018 4:09 PM
So I have a couple of dilemmas. I can create a good table for dbo.Competitors.
CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20), FirstName varchar(20), LastName varchar(20), FullName AS LastName + ', ' + FirstName PERSISTED, Level varchar(20), AgeGroup varchar(20), Session int, USAGNum int, CompNum int, ClubName varchar(50), Gender char(1), CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO
but I need to create a dbo.participant table that imports from an additional xml file and gets data from the dbo.competitors table also. Event, AvgScore & Bscore
CREATE TABLE dbo.ParticipantList ( EventID int REFERENCES dbo.EventList(EventID), CompetitorID int REFERENCES dbo.Competitors(CompetitorID), SessionID int, Type varchar(20), AgeGroup varchar(20), CompNum int, event varchar(10), avgscore float, bscore float, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SessionID ASC ) ); GO
My other issue is I have 14 xml files that have all the data. The names stay the same. They are the following...2-10/xb/xs/xg/xp/xd(WomenResultsDay1). Not all files have data as the scoring software only outputs selected sessions and levels. So I either need to import all possible files and have NULL values and then select the session and levels for outputs or select the session and levels by a check box and then import the data? It seams to be getting complicated.
DECLARE @x xml
SELECT @x=P FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
INSERT INTO dbo.ParticipantList SELECT*
FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1) WITH(
event varchar(10), avgscore float, bscore float);
As the data in the XML files is quite likely NOT normalized, adapting to normalized table structures will require some additional handling, but should be well worth it in the long run. You can always import the XML files to separate staging tables that just take the data "as is", and then you can "transform" the data into normalized form with queries that you create for that purpose. As to the foreign key difficulties you ran into, that's kind of the way it's supposed to work. You wouldn't want to drop a table that some other table is dependent on, at least most of the time, so yes, for testing purposes, you always want to do it in reverse order from how the tables were created. Foreign key relationships help keep bad data out of your database. That's why you would need to load the address data first, which then supports the other tables.
So since I will be restarting the program for every new session (up to 10 over 3 days) should I not drop the tables and instead erase the data? There are changes to the athletes right up to start time so I need to have the most up to date data for each session.

How can I import all 14 files at once? Do I need to

TheFirstOne - Wednesday, May 16, 2018 12:27 PM
sgmunson - Wednesday, May 16, 2018 6:52 AM
TheFirstOne - Tuesday, May 15, 2018 4:09 PM
So I have a couple of dilemmas. I can create a good table for dbo.Competitors.
CREATE TABLE dbo.Competitors ( CompetitorID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Type varchar(20), FirstName varchar(20), LastName varchar(20), FullName AS LastName + ', ' + FirstName PERSISTED, Level varchar(20), AgeGroup varchar(20), Session int, USAGNum int, CompNum int, ClubName varchar(50), Gender char(1), CONSTRAINT CK_Competitors_Gender_MF CHECK (Gender IN ('M', 'F')) ); GO
but I need to create a dbo.participant table that imports from an additional xml file and gets data from the dbo.competitors table also. Event, AvgScore & Bscore
CREATE TABLE dbo.ParticipantList ( EventID int REFERENCES dbo.EventList(EventID), CompetitorID int REFERENCES dbo.Competitors(CompetitorID), SessionID int, Type varchar(20), AgeGroup varchar(20), CompNum int, event varchar(10), avgscore float, bscore float, CONSTRAINT PK_ParticipantList_EventID_CompetitorID PRIMARY KEY CLUSTERED ( EventID ASC, CompetitorID ASC, SessionID ASC ) ); GO
My other issue is I have 14 xml files that have all the data. The names stay the same. They are the following...2-10/xb/xs/xg/xp/xd(WomenResultsDay1). Not all files have data as the scoring software only outputs selected sessions and levels. So I either need to import all possible files and have NULL values and then select the session and levels for outputs or select the session and levels by a check box and then import the data? It seams to be getting complicated.
DECLARE @x xml
SELECT @x=P FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @x
INSERT INTO dbo.ParticipantList SELECT*
FROM OPENXML (@hdoc, '/MeetResultsMsg/AthleteResults/Athlete/Scores/Score', 1) WITH(
event varchar(10), avgscore float, bscore float);
As the data in the XML files is quite likely NOT normalized, adapting to normalized table structures will require some additional handling, but should be well worth it in the long run. You can always import the XML files to separate staging tables that just take the data "as is", and then you can "transform" the data into normalized form with queries that you create for that purpose. As to the foreign key difficulties you ran into, that's kind of the way it's supposed to work. You wouldn't want to drop a table that some other table is dependent on, at least most of the time, so yes, for testing purposes, you always want to do it in reverse order from how the tables were created. Foreign key relationships help keep bad data out of your database. That's why you would need to load the address data first, which then supports the other tables.
So since I will be restarting the program for every new session (up to 10 over 3 days) should I not drop the tables and instead erase the data? There are changes to the athletes right up to start time so I need to have the most up to date data for each session.

So to import all the data do I run 14 separate imports and append to the tables?
SELECT @x=P

FROM OPENROWSET (BULK 'C:/proscore5/2WomenResultsDay1.xml', SINGLE_BLOB) AS Product(P)

Or is there a better way?

sgmunson SSC Guru Points: 110651 More actions · Answer 11

Well, I'm not sure why you would want to drop existing data as it would likely see re-use. Here's perhaps the critical question or two: Can you export pretty much whatever you might need, or are you only getting scoring data? The design I presented was based on a fairly complete solution, and if you are only getting scores, much of the relationship data that would be needed may well not exist in the data you are getting. And thus, I may have the wrong impression about your role in the competition administration. Unless we could derive the relationship data from the scoring data, there may be no easy way to populate the tables. You have 14 xml files... We'll have to look at each one's contents and see what we have. Are the exports purely scoring data? Is each export tied to a specific event? Might there be separate exports for a quarter-final, a semi-final, and final, for a single event? And are the xml files all differing in their schema? Most sport competitions I've ever been to always had at least printed scoresheets for each judged event. Are such provided, or are the scores only presented on scoreboards, and nothing ever printed out? Are you looking to just print scoresheets?

I ask these questions because trying to organize results without the existing relationships between participants and the events they've registered for could be quite challenging, especially when you have any really similar competitor names. Tying results together in any meaningful way might be impractical. I can't recall if you've actually posted any of the xml files, and this might well take a level of effort that's more than what a volunteer on this site might be able to spend. Let me know if you've posted any of the files and I'll try to take a look tonight after I get home from work.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TheFirstOne SSCommitted Points: 1659 More actions · Answer 12

There are two vendors that post scores online. Two issues happen that make it difficult sometimes to get the scores. Most meets are in steel buildings so cell coverage is iffy and most venues don't let parents use the WiFi. The second one is you have to search through a large amount of data to get to the current meet and going back and forth through sessions is difficult. So we are trying to simplify everything by broadcasting the results on a local wifi system through an app. There are 14 xml files that are created that have the scoring. There is one file that has all the athletes info and one file that has the rotation plus team. I am attaching a sample file, this would be one of the 14 scoring files. The data will repeat for how many athletes are in a session. It really does not need to be complicated but the way I was coding before was beginnerish so I was hoping to clean it up a little a learn a bit. Here is how I score a event.

Start scoring software
Link 4 event ipads through wifi to the scoring computer
Each judge enters a score on the ipad
The scoring software flashes the scores on a scoreboard and upload the scores to the two vendors.
The scoring get updated when either a new score is entered or a time limit occurs.

All Files
2-10WomenResultsDay1 and XBWomenResultsDay1, XSWomenResultsDay1, XGWomenResultsDay1, XPWomenResultsDay1, XDWomenResultsDay1, AthleteListDay1, WomenStartListDay1, WomenTeamDay1

I put together some code that will read all files in a director and put it in a table.
USE meetscoreslive

GO CREATE TABLE dbo.XMLFilesTable ( Id INT IDENTITY PRIMARY KEY, FileName VARCHAR(100), XMLData XML, LoadedDateTime DATETIME )

IF OBJECT_ID('tempdb..#FileList') IS NOT NULL DROP TABLE #FileList

--Folder path where files are present Declare @SourceFolder VARCHAR(100) SET @SourceFolder='C:\Proscore5\xml\'

CREATE TABLE #FileList ( Id int identity(1,1), FileName nvarchar(255), Depth smallint, FileFlag bit)

--Load the file names from a folder to a table INSERT INTO #FileList (FileName,Depth,FileFlag) EXEC xp_dirtree @SourceFolder, 10, 1

--Use Cursor to loop throught files --Select * From #FileList Declare @FileName VARCHAR(500)

DECLARE Cur CURSOR FOR SELECT FileName from #FileList where fileflag=1

OPEN Cur FETCH Next FROM Cur INTO @FileName WHILE @@FETCH_STATUS = 0 BEGIN

DECLARE @InsertSQL NVARCHAR(MAX)=NULL --Prepare SQL Statement for insert SET @InsertSQL= 'INSERT INTO dbo.XMLFilesTable(FileName, LoadedDateTime,XMLData) SELECT '''+@FileName+''',getdate(),Convert(XML,BulkColumn ) As BulkColumn FROM Openrowset( Bulk '''+@SourceFolder+@FileName+''', Single_Blob) as Image'

--Print and Execute SQL Insert Statement to load file Print @InsertSQL EXEC(@InsertSQL)

FETCH Next FROM Cur INTO @FileName END CLOSE Cur DEALLOCATE Cur

but it reads all the files, xml, html etc. so I had to create a xml directory and store only the 14 scoring files. How would I select only the 14 files I need? It also only creates one table with filename, xmldata and date for colums. The data is all there in the xmldata column but not in separate columns like I have with my regular imports. Should I figure how to modify it to have the columns or can I read the data into new tables from the xmldata column?

sgmunson SSC Guru Points: 110651 More actions · Answer 13

Okay, had a chance to look at this, and the following might get you started. I couldn't quite figure out how to BULK IMPORT the file, so I decided to paste the file data into a VALUES clause to get it inserted so that I could start playing around with the XML. Here's what I came up with:
CREATE TABLE #Test ( ScoresID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, ScoresXML xml );

INSERT INTO #Test (ScoresXML) VALUES ('<?xml version="1.0" encoding="ISO-8859-1"?> <MeetResultsMsg Type="ARTW3" EventID="" Session="1" > <AthleteResults> <Athlete Type="ARTW3" CompNum="301" LastName="Arthur" FirstName="Caitlin" 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> <Athlete Type="ARTW3" CompNum="302" LastName="Chollar" FirstName="Maya" AG="SRB"> <Scores> <Score event="1" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="2" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="3" avgscore="9.350" bscore="9.350"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="4" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.350"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="AA" avgscore="37.125"/> </Scores> <Places> <Place event="AA" rank="2" order="2"/> <Place event="1" rank="6" order="6"/> <Place event="2" rank="11" order="11"/> <Place event="3" rank="2" order="2"/> <Place event="4" rank="6" order="6"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="303" LastName="Dallmann" FirstName="Addie" AG="SRA"> <Scores> <Score event="1" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="37.075"/> </Scores> <Places> <Place event="AA" rank="3" order="3"/> <Place event="1" rank="8" order="8"/> <Place event="2" rank="6" order="6"/> <Place event="3" rank="4" order="4"/> <Place event="4" rank="8" order="8"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="304" LastName="Frame" FirstName="Janna" AG="JRA"> <Scores> <Score event="1" avgscore="9.150" bscore="9.150"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="3" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="37.025"/> </Scores> <Places> <Place event="AA" rank="4" order="4"/> <Place event="1" rank="15" order="15"/> <Place event="2" rank="2" order="2"/> <Place event="3" rank="6" order="6"/> <Place event="4" rank="6" order="7"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="305" LastName="Hartman" FirstName="Savanna" AG="JRA"> <Scores> <Score event="1" avgscore="9.450" bscore="9.450"> <JudgeScore id="1" score="9.400"/> <JudgeScore id="2" score="9.500"/> </Score> <Score event="2" avgscore="9.075" bscore="9.075"> <JudgeScore id="1" score="9.150"/> <JudgeScore id="2" score="9.000"/> </Score> <Score event="3" avgscore="9.025" bscore="9.025"> <JudgeScore id="1" score="9.000"/> <JudgeScore id="2" score="9.050"/> </Score> <Score event="4" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.875"/> </Scores> <Places> <Place event="AA" rank="5" order="5"/> <Place event="1" rank="2" order="2"/> <Place event="2" rank="19" order="19"/> <Place event="3" rank="12" order="12"/> <Place event="4" rank="4" order="4"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="306" LastName="Higgins" FirstName="Caitlin" AG="JRB"> <Scores> <Score event="1" avgscore="9.100" bscore="9.100"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="2" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="3" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.250"/> </Score> <Score event="4" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="AA" avgscore="36.850"/> </Scores> <Places> <Place event="AA" rank="6" order="6"/> <Place event="1" rank="20" order="20"/> <Place event="2" rank="12" order="12"/> <Place event="3" rank="9" order="9"/> <Place event="4" rank="3" order="3"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="307" LastName="Hurley" FirstName="Kaitlyn" AG="SRB"> <Scores> <Score event="1" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.400"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="2" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="4" avgscore="8.825" bscore="8.825"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="8.750"/> </Score> <Score event="AA" avgscore="36.825"/> </Scores> <Places> <Place event="AA" rank="7" order="7"/> <Place event="1" rank="3" order="3"/> <Place event="2" rank="4" order="4"/> <Place event="3" rank="6" order="7"/> <Place event="4" rank="26" order="26"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="308" LastName="Khan" FirstName="Aneesa" AG="SRB"> <Scores> <Score event="1" avgscore="9.275" bscore="9.275"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.200" bscore="9.300" adj="-0.100"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.050" bscore="9.050"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="AA" avgscore="36.825"/> </Scores> <Places> <Place event="AA" rank="7" order="8"/> <Place event="1" rank="10" order="10"/> <Place event="2" rank="6" order="7"/> <Place event="3" rank="6" order="8"/> <Place event="4" rank="19" order="19"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="309" LastName="Kilgallen" FirstName="Riley" AG="SRB"> <Scores> <Score event="1" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="3" avgscore="9.000" bscore="9.000"> <JudgeScore id="1" score="8.950"/> <JudgeScore id="2" score="9.050"/> </Score> <Score event="4" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="AA" avgscore="36.650"/> </Scores> <Places> <Place event="AA" rank="9" order="9"/> <Place event="1" rank="8" order="9"/> <Place event="2" rank="12" order="13"/> <Place event="3" rank="13" order="13"/> <Place event="4" rank="12" order="12"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="311" LastName="Martin" FirstName="Haylee" AG="SRA"> <Scores> <Score event="1" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="2" avgscore="8.885" bscore="8.885"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="8.870"/> </Score> <Score event="3" avgscore="9.000" bscore="9.000"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="4" avgscore="9.100" bscore="9.100"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="AA" avgscore="36.310"/> </Scores> <Places> <Place event="AA" rank="10" order="10"/> <Place event="1" rank="6" order="7"/> <Place event="2" rank="25" order="25"/> <Place event="3" rank="13" order="14"/> <Place event="4" rank="17" order="17"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="313" LastName="Oberschmid" FirstName="Madison" AG="SRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="315" LastName="Shaw" FirstName="Jolan" AG="SRB"> <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> <Athlete Type="ARTW3" CompNum="316" LastName="Shreve" FirstName="Ava" AG="JRA"> <Scores> <Score event="1" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="2" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="3" avgscore="9.350" bscore="9.350"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="4" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.350"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="AA" avgscore="37.125"/> </Scores> <Places> <Place event="AA" rank="2" order="2"/> <Place event="1" rank="6" order="6"/> <Place event="2" rank="11" order="11"/> <Place event="3" rank="2" order="2"/> <Place event="4" rank="6" order="6"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="317" LastName="Swenson" FirstName="Sarah" AG="JRB"> <Scores> <Score event="1" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="37.075"/> </Scores> <Places> <Place event="AA" rank="3" order="3"/> <Place event="1" rank="8" order="8"/> <Place event="2" rank="6" order="6"/> <Place event="3" rank="4" order="4"/> <Place event="4" rank="8" order="8"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="318" LastName="Zahara" FirstName="Leah" AG="SRB"> <Scores> <Score event="1" avgscore="9.150" bscore="9.150"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="3" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.250" bscore="9.250"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="37.025"/> </Scores> <Places> <Place event="AA" rank="4" order="4"/> <Place event="1" rank="15" order="15"/> <Place event="2" rank="2" order="2"/> <Place event="3" rank="6" order="6"/> <Place event="4" rank="6" order="7"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="319" LastName="Arcuino" FirstName="Annalise" AG="Child"> <Scores> <Score event="1" avgscore="9.450" bscore="9.450"> <JudgeScore id="1" score="9.400"/> <JudgeScore id="2" score="9.500"/> </Score> <Score event="2" avgscore="9.075" bscore="9.075"> <JudgeScore id="1" score="9.150"/> <JudgeScore id="2" score="9.000"/> </Score> <Score event="3" avgscore="9.025" bscore="9.025"> <JudgeScore id="1" score="9.000"/> <JudgeScore id="2" score="9.050"/> </Score> <Score event="4" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.875"/> </Scores> <Places> <Place event="AA" rank="5" order="5"/> <Place event="1" rank="2" order="2"/> <Place event="2" rank="19" order="19"/> <Place event="3" rank="12" order="12"/> <Place event="4" rank="4" order="4"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="321" LastName="Canavera" FirstName="Ally" AG="SRA"> <Scores> <Score event="1" avgscore="9.100" bscore="9.100"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="2" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="3" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.100"/> <JudgeScore id="2" score="9.250"/> </Score> <Score event="4" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="AA" avgscore="36.850"/> </Scores> <Places> <Place event="AA" rank="6" order="6"/> <Place event="1" rank="20" order="20"/> <Place event="2" rank="12" order="12"/> <Place event="3" rank="9" order="9"/> <Place event="4" rank="3" order="3"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="323" LastName="Eccles" FirstName="Tanya" AG="SRA"> <Scores> <Score event="1" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.400"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="2" avgscore="9.400" bscore="9.400"> <JudgeScore id="1" score="9.500"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.200" bscore="9.200"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="4" avgscore="8.825" bscore="8.825"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="8.750"/> </Score> <Score event="AA" avgscore="36.825"/> </Scores> <Places> <Place event="AA" rank="7" order="7"/> <Place event="1" rank="3" order="3"/> <Place event="2" rank="4" order="4"/> <Place event="3" rank="6" order="7"/> <Place event="4" rank="26" order="26"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="324" LastName="Inagawa" FirstName="Trudy" AG="SRB"> <Scores> <Score event="1" avgscore="9.275" bscore="9.275"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="3" avgscore="9.200" bscore="9.300" adj="-0.100"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="4" avgscore="9.050" bscore="9.050"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="AA" avgscore="36.825"/> </Scores> <Places> <Place event="AA" rank="7" order="8"/> <Place event="1" rank="10" order="10"/> <Place event="2" rank="6" order="7"/> <Place event="3" rank="6" order="8"/> <Place event="4" rank="19" order="19"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="325" LastName="Kelley" FirstName="Elizabeth" AG="JRA"> <Scores> <Score event="1" avgscore="9.300" bscore="9.300"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.300"/> </Score> <Score event="2" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="3" avgscore="9.000" bscore="9.000"> <JudgeScore id="1" score="8.950"/> <JudgeScore id="2" score="9.050"/> </Score> <Score event="4" avgscore="9.175" bscore="9.175"> <JudgeScore id="1" score="9.200"/> <JudgeScore id="2" score="9.150"/> </Score> <Score event="AA" avgscore="36.650"/> </Scores> <Places> <Place event="AA" rank="9" order="9"/> <Place event="1" rank="8" order="9"/> <Place event="2" rank="12" order="13"/> <Place event="3" rank="13" order="13"/> <Place event="4" rank="12" order="12"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="326" LastName="Knight" FirstName="Ella" AG="SRA"> <Scores> <Score event="1" avgscore="9.325" bscore="9.325"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="9.350"/> </Score> <Score event="2" avgscore="8.885" bscore="8.885"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="8.870"/> </Score> <Score event="3" avgscore="9.000" bscore="9.000"> <JudgeScore id="1" score="8.900"/> <JudgeScore id="2" score="9.100"/> </Score> <Score event="4" avgscore="9.100" bscore="9.100"> <JudgeScore id="1" score="9.300"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="AA" avgscore="36.310"/> </Scores> <Places> <Place event="AA" rank="10" order="10"/> <Place event="1" rank="6" order="7"/> <Place event="2" rank="25" order="25"/> <Place event="3" rank="13" order="14"/> <Place event="4" rank="17" order="17"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="329" LastName="Levitt" FirstName="Ashley" AG="JRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="331" LastName="MacFarlan" FirstName="Bryn" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="332" LastName="MacFarlan" FirstName="Natalie" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="333" LastName="Mockford" FirstName="Holly" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="334" LastName="Montermoso" FirstName="Mailia" AG="JRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="335" LastName="Norling" FirstName="Stella" AG="JRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="336" LastName="Novotny" FirstName="Cameron" AG="JRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="337" LastName="Pachuilo" FirstName="Kylee" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="338" LastName="Rabon" FirstName="Katelyn" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="339" LastName="Robinson" FirstName="Jj" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="340" LastName="Seed" FirstName="Simone" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="341" LastName="Sun" FirstName="Ruth" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="342" LastName="To" FirstName="Elliana" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="343" LastName="Wahl" FirstName="Livia" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="344" LastName="Wayts" FirstName="Lilly" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="345" LastName="Pilot" FirstName="Erin" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="346" LastName="Zhang" FirstName="Renee" AG="SRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="347" LastName="Anderson" FirstName="Emma" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="348" LastName="Anderson" FirstName="Josie" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="349" LastName="Beringer" FirstName="Arianna" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="350" LastName="Borrayo" FirstName="Sophia" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="351" LastName="Geelan" FirstName="Lenore" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="352" LastName="Hinds" FirstName="Sarah" AG="SRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="353" LastName="Hubbard" FirstName="Ava" AG="SRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="354" LastName="Huntingford" FirstName="Breanne" AG="JRB"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="355" LastName="Reed" FirstName="Lily" AG="JRA"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> <Athlete Type="ARTW3" CompNum="357" LastName="Woodley" FirstName="Layla" AG="Child"> <Scores> <Score event="1" avgscore="8.850" bscore="8.850"> <JudgeScore id="1" score="8.800"/> <JudgeScore id="2" score="8.900"/> </Score> <Score event="2" avgscore="9.425" bscore="9.425"> <JudgeScore id="1" score="9.450"/> <JudgeScore id="2" score="9.400"/> </Score> <Score event="3" avgscore="8.700" bscore="8.700"> <JudgeScore id="1" score="8.600"/> <JudgeScore id="2" score="8.800"/> </Score> <Score event="4" avgscore="9.225" bscore="9.225"> <JudgeScore id="1" score="9.250"/> <JudgeScore id="2" score="9.200"/> </Score> <Score event="AA" avgscore="36.200"/> </Scores> <Places> <Place event="AA" rank="11" order="11"/> <Place event="1" rank="32" order="32"/> <Place event="2" rank="2" order="3"/> <Place event="3" rank="28" order="28"/> <Place event="4" rank="8" order="9"/> </Places> </Athlete> </AthleteResults> </MeetResultsMsg> ');

WITH CompScores AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG , sc.s.value('@event', 'varchar(3)') AS [event], sc.s.value('@avgscore', 'varchar(6)') AS avgscore, sc.s.value('@bscore', 'varchar(6)') AS bscore , jsc.js.value('@id', 'varchar(2)') AS JudgeID, jsc.js.value('@score', 'varchar(6)') AS rawscore FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Scores/Score') sc(s) OUTER APPLY sc.s.nodes('./JudgeScore') jsc(js) ), CompPlaces AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG , pl.p.value('@event', 'varchar(3)') AS [event], pl.p.value('@rank', 'varchar(3)') AS PlaceRank, pl.p.value('@order', 'varchar(6)') AS PlaceOrder FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Places/Place') pl(p) ) SELECT CS1.ScoresID, CS1.[Type], CS1.AG AS AgeGroup, CS1.CompNum, CS1.LastName, CS1.FirstName, CS1.[event], CS1.avgscore, CS1.bscore, CS1.rawscore AS Judge1RawScore, CS2.rawscore AS Judge2RawScore, CP.PlaceOrder, CP.PlaceOrder FROM (SELECT * FROM CompScores WHERE JudgeID = '1' OR [event] = 'AA') AS CS1 INNER JOIN (SELECT * FROM CompScores WHERE JudgeID = '2' OR [event] = 'AA') AS CS2 ON CS1.CompNum = CS2.CompNum AND CS1.[event] = CS2.[event] LEFT OUTER JOIN CompPlaces AS CP ON CS1.CompNum = CP.CompNum AND CS1.[event] = CP.[event]

DROP TABLE #Test;

You would need to insert ALL the scoring files before running the query, but if there are variations on the number of Judges, that would require some changes to the query. Again, this is a starter. See what you think and post back with questions.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

TheFirstOne SSCommitted Points: 1659 More actions · Answer 14

Ok, that just fried my brain. I have been looking up info all day but still having trouble as this is above my pay grade 😉 I understand what it is doing but there are a whole bunch of new terms.

WITH CompScores AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG, sc.s.value('@event', 'varchar(3)') AS [event], sc.s.value('@avgscore', 'varchar(6)') AS avgscore, sc.s.value('@bscore', 'varchar(6)') AS bscore, jsc.js.value('@id', 'varchar(2)') AS JudgeID, jsc.js.value('@score', 'varchar(6)') AS rawscore FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Scores/Score') sc(s) OUTER APPLY sc.s.nodes('./JudgeScore') jsc(js) ), CompPlaces AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG, pl.p.value('@event', 'varchar(3)') AS [event], pl.p.value('@rank', 'varchar(3)') AS PlaceRank, pl.p.value('@order', 'varchar(6)') AS PlaceOrder FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Places/Place') pl(p) ) SELECT CS1.ScoresID, CS1.[Type], CS1.AG AS AgeGroup, CS1.CompNum, CS1.LastName, CS1.FirstName, CS1.[event], CS1.avgscore, CS1.bscore, CS1.rawscore AS Judge1RawScore, CS2.rawscore AS Judge2RawScore, CP.PlaceOrder, CP.PlaceOrder FROM (SELECT * FROM CompScores WHERE JudgeID = '1' OR [event] = 'AA') AS CS1 INNER JOIN (SELECT * FROM CompScores WHERE JudgeID = '2' OR [event] = 'AA') AS CS2 ON CS1.CompNum = CS2.CompNum AND CS1.[event] = CS2.[event] LEFT OUTER JOIN CompPlaces AS CP ON CS1.CompNum = CP.CompNum AND CS1.[event] = CP.[event]

INNER JOIN?
LEFT OUTER JOIN?
CROSS APPLY?
OUTER APPLY?
Multiple c.p.value and jsc.js.value variables, don't they have to be different names?
Where are the CS1, CS2 and CP declared? I think the are CompScores and CompPlaces but are abbreviated.
c(p) & pl(p) & jsc(js) & sc(s)
SELECT t.ScoresID? What does the t do?
Where are you declaring CompScores and CompPlaces or do you not have to?

On the SELECT statement it looks like the first part calls the titles and then the FROM statement calls the data? Sorry if I seem clueless. Now this (your code) would all work if I could merge all the xml files and then import them. I can do this with any number of query's which will import the data into a new table but I was having trouble finding a query that can take the data from table.column and insert it into the temp table.

INSERT INTO #Test (ScoresXML) VALUES
I tried various query's with no success
The following query will create a table with three columns. The 3rd column has all the xml data from the 14 files. How can I insert the data into the #test table?

INSERT INTO #Test (ScoresXML)
VALUES <-----------Need to insert dbo.XMLImport.column.xmldata

Tim

TheFirstOne SSCommitted Points: 1659 More actions · Answer 15

I like that your code gets all the data in from the xml file. Maybe for simplicity I should have one long query that imports 14 separate xml files to #temp tables?

USE meetscoreslive

CREATE TABLE #Test ( ScoresID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, ScoresXML xml );

Import from 2WomenResultsDay1.xml, not sure how yet.

WITH CompScores AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG, sc.s.value('@event', 'varchar(3)') AS [event], sc.s.value('@avgscore', 'varchar(6)') AS avgscore, sc.s.value('@bscore', 'varchar(6)') AS bscore, jsc.js.value('@id', 'varchar(2)') AS JudgeID, jsc.js.value('@score', 'varchar(6)') AS rawscore FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Scores/Score') sc(s) OUTER APPLY sc.s.nodes('./JudgeScore') jsc(js) ), CompPlaces AS (

SELECT t.ScoresID, c.p.value('@Type', 'varchar(20)') AS [Type], c.p.value('@CompNum', 'varchar(20)') AS CompNum, c.p.value('@LastName', 'varchar(30)') AS LastName, c.p.value('@FirstName', 'varchar(20)') AS FirstName, c.p.value('@AG', 'varchar(5)') AS AG, pl.p.value('@event', 'varchar(3)') AS [event], pl.p.value('@rank', 'varchar(3)') AS PlaceRank, pl.p.value('@order', 'varchar(6)') AS PlaceOrder FROM #Test AS t CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p) OUTER APPLY c.p.nodes('./Places/Place') pl(p) ) SELECT CS1.ScoresID, CS1.[Type], CS1.AG AS AgeGroup, CS1.CompNum, CS1.LastName, CS1.FirstName, CS1.[event], CS1.avgscore, CS1.bscore, CS1.rawscore AS Judge1RawScore, CS2.rawscore AS Judge2RawScore, CP.PlaceOrder, CP.PlaceOrder FROM (SELECT * FROM CompScores WHERE JudgeID = '1' OR [event] = 'AA') AS CS1 INNER JOIN (SELECT * FROM CompScores WHERE JudgeID = '2' OR [event] = 'AA') AS CS2 ON CS1.CompNum = CS2.CompNum AND CS1.[event] = CS2.[event] LEFT OUTER JOIN CompPlaces AS CP ON CS1.CompNum = CP.CompNum AND CS1.[event] = CP.[event]

Output to a table called dbo.2WomenResultsDay1

Then drop #Temp Tables and and import 32WomenResultsDay1.xml

Tim