Importing from XML to SQL 2014

Question

Importing from XML to SQL 2014

TheFirstOne

SSCommitted

Points: 1659
More actions
May 1, 2018 at 4:57 pm

#334571
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

Viewing 15 posts - 1 through 15 (of 127 total)

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

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

When you say you want to get it into the database what do you mean? SQL Server has an xml datatype, so do you mean simply store the xml in a table with a column of that datatype? That would just need a simple INSERT statement.

Thom~

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

Rick-153145 SSCrazy Points: 2823 More actions · Answer 2

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).

TheFirstOne SSCommitted Points: 1659 More actions · Answer 3

Ok so I need to declare the table first. Here is how the program works. There is the main scoring software that the judges enter the individual scores for. The company that made the software has a script that output the results via xml. You can change the time from 1sec to 300sec for the updates but it either rewrites the entire xml and replaces the old one with the same name or it opens the xml and makes the changes. Since I do not know how it is done I will need to read the same file multiple times. Should I put in a condition before I create the table to verify there is a table and it has data in it, or should I use this one script to create the first tables and then execute another one that leave the table creating out?

Thank you for your input
Tim

TheFirstOne SSCommitted Points: 1659 More actions · Answer 4

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

Yes, I was looking at the insert command but all the examples I looked at on youtube had the data being
added within the query. The below modification worked.

TheFirstOne SSCommitted Points: 1659 More actions · Answer 5

I have been reading that it is not necessary to sort the table? Is that true. I was thinking of sorting the table by AgeGroup/event/avgscore and then outputting the table as an xml/json file which can then be read by the app software? I can sort the data with this in the query

SELECT FirstName, LastName, AgeGroup, CompNumber, event, avgscore, bscore
FROM WomenResults
ORDER BY AgeGroup, event, avgscore DESC;

but the table has the data just as it was in the file. The goal is the separate the athletes so the parents can view scoring by their daughters agegroup.

Tim

sgmunson SSC Guru Points: 110639 More actions · Answer 6

TheFirstOne - Wednesday, May 2, 2018 10:59 AM
I have been reading that it is not necessary to sort the table? Is that true. I was thinking of sorting the table by AgeGroup/event/avgscore and then outputting the table as an xml/json file which can then be read by the app software? I can sort the data with this in the query
SELECT FirstName, LastName, AgeGroup, CompNumber, event, avgscore, bscore
FROM WomenResults
ORDER BY AgeGroup, event, avgscore DESC;
but the table has the data just as it was in the file. The goal is the separate the athletes so the parents can view scoring by their daughters agegroup.
Tim

Okay, but what, exactly, does "separate the athletes so the parents can view scoring by their daughter age group" mean? When you run a query, the results come back to you in SSMS as a grid or as text, depending on which way you have that tool configured. Using ORDER BY just determines which order the rows in the table are returned. As we only have data for what appears to be one athlete, there's not much testing we can do, nor do we have any idea what it means for you to be able to "show the parents" whatever you want to show them. What, exactly, do you do with the results of that query? Slap it into a spreadsheet and project a view of it onto a monitor somewhere? What about that scoring software? Does it provide some display capacity that could organize the data by age group? We need a lot more information...

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 7

Ok, So here is a little more detail. We have a program (one that I cannot have access to the code) that runs the meets. Judges enter scores and tallies everything and at the end of the meet we print up reports and give out awards. This software output results on the fly to some vendors that repackage it to their web sites. Parents view the results on apps on their phones. Unfortunately our meets are in steel buildings and getting connections is difficult. Also you have to navigate through all meets in the US to find the one your are at. We want to bypass this slowness and broadcast the scores locally. While we do have score boards, the scores only last for 30 sec and we cannot show top scores in each event and age group. The software company that make the scoring program has a small program that output the scores in xml which they let NBC use in college and up so I have to make do with what I have. . I am not using SSMS I think, as I could not figure out how to install and make it work. It was easier to just write the code line by line. I coded html in the past and usually write in a text editor and not a package program. Here is what I have done up to date

A query to create all the tables(QCreateTables). There is one table for each age group and 4 events plus the AA(all around) scores.(about 25 tables)
A query to delete all tables(QDeleteTables).
A query to import the session xml and break it up into the age group/events tables(QWomesResults). This query is running in a loop that I will assign a button to.
The query is running in a loop as the scoring software is updating the xml output every 5-300 seconds.

Programming I need to do.

Add to the import query an output to JSON for each age group/event table.
Create a program that selects the deletes the tables, create new tables, import the data, splits age groups, splits events and outputs to JSON.
Add an Athletes query
Add an Rotation query.

I am an old Access programmer so I downloaded Visual studio 2017 and plan to create a program to first delete any existing tables, create the new ones, add the data and output JSON files to a local web serer where our App can access. Our app will have a look up for either lastname, competition number or USAG number. They will then be able to see each events score and where they rank in their age group for each event.

Tim

Thom A SSC Guru Points: 99126 More actions · Answer 8

Ok, let's start with the first bullet point: A query to create all the tables(QCreateTables).This implies you have DDL for your table(s). Could you post that please? We then know what the XML data needs to look like to be able to be inserted into those tables.

You also keep mention ion JSON, however, we've only seen XML so far. Which is it? If it's JSON, SQL Server 2014 is going to be a problem (JSON functions were only introduced with SQL Server 2016). JSON and XML are completely different, the acronyms are not interchangeable, so please don't say one when you mean the other.

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 9

I am attaching the three queries I have which are not finished yet. The JSON is the format that the app designers want. Please remember this is my first week learning SQL so I need time to get up to speed. I was just assuming I could output to JSON with the 2014 version. Once again thank you for your input.

Thom A SSC Guru Points: 99126 More actions · Answer 10

TheFirstOne - Friday, May 4, 2018 10:39 AM
I was just assuming I could output to JSON with the 2014 version.

Unfortunately not: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017. Notice that the topic applies to SQL Server (starting with 2016).If you have to be able to export to JSON you'll need to use a more recent version of SQL Server, or use a different tool which can integrate with SQL Server and export JSON.

I've only opened the first file, but that is a lot of tables there which seems to have the same DDL definition. This seems like a poor design choice. You shouldn't be separating data into separate tables because they are attached to a different event, or Age Group; doing so makes your solution non-scalable. You should be storing all the data in a single table and then using a column to denote the specific detail. If you then only need specific data, instead of querying the "correct" table, you simply use a WHERE clause. This is a common mistake by beginniners, however, it's something that you need to get out of the habit of doing very early on. You'll find, otherwise, that you'll be entering a "world of pain" in the future otherwise; especially if your data begins to change.

If you really need to do something more "drastic" (because you have a much larger amount of data), you can also look into Table Partitioning, however, that isn't something that I recommend for a beginning, nor for the amount of data you are likely to have,

To put this a little in perspective, one thing I've seen a lot of is people separate data for years into different tables. So their DDL definition looks something like:

CREATE TABLE dbo.Sales_2017
  (SaleID int IDENTITY(1,1),
  CustomerID int,
  SaleDate date,
  SaleItem1ID int, --Did this on purpose to show further bad non-NF practices
  SaleItem2ID int,
  SaleItem3ID int,
  SaleItem4ID int,
  SaleItem5ID int);
          
CREATE TABLE dbo.Sales_2018
  (SaleID int IDENTITY(1,1),
  CustomerID int,
  SaleDate date,
  SaleItem1ID int, --Did this on purpose to show further bad non-NF practices
  SaleItem2ID int,
  SaleItem3ID int,
  SaleItem4ID int,
  SaleItem5ID int);

The first problem here is you have 2 tables for 2 separate years. The other is that there's a repeating field for the item ID; meaning a single sale is not limited to 5 items; not great.

The DDL should really look like this:

CREATE TABLE dbo.Sale
  (SaleID int IDENTITY(1,1),
  CustomerID int,
  SaleDate date,
  SaleYear AS DATEPART(YEAR, SaleDate) PERSISTED); --A computed Column instead,as it can be derived from Sale Date, although it doesn't have to be
CREATE TABLE dbo.SaleItem --And a further table for the Many to Many relationship
  (SaleItemID int IDENTITY(1,1),
  SaleID int,
  ItemID int);

This firstly means that the Sale table doesn't need to have a new one made every year. It also means you likely don't need to create dynamic SQL later down the line to query your data. As for the item, it now has a (what i refer to as) a composition key table. It's effectively just a list of foreign keys that link the Sale and Item tables, as they have a Many to Many relationship.

Of course, these are overly simplified; there's no Foreign keys or Primary keys for example. This, however, is one of the fundamental mind sets you need to use when designing tables in a Relational Database.

I haven't ventured too far into the text files, but i think we really need to start at the basics first and fix that problem before we even consider translating the XML into something but tabular.

Does the above make sense, and are you happy to entertain this?

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 11

Yes, I do want to code the most efficiently, just beginning like you said. So to start over here are the different tables I will have.

This table has all the data that the scoring software outputs in xml. There are 14 possible combinations. You can have multiple levels during a session so I would code a check box to select the levels. The level is in the Type variable. I was just going to create 14 different programs and then have a drop down box to select, not efficient but the best I have for now.
Level2,Level3,Level4,Level5,Level6,Level7,Level8,Level9,Level10,LevelXB,LevelXS,LevelXG,LevelXP,LevelXD

CREATE TABLE lvl3WomenResultsDay1
(
Type varchar(50),
CompNum int ,
FirstName varchar(50),
LastName varchar(50),
AgeGroup varchar(10),
event varchar(10),
avgscore float,
bscore float);

The next table is a list of all Athletes in the meet for all session. I am adding it as we may need the data in the future.
CREATE TABLE AthleteListDay1
(
Type varchar(50),
Session int,
Level varchar(10),
CompNum int,
USAGNum int,
FirstName varchar(50),
LastName varchar(50),
ClubName varchar(50)
)

The next table is to hold scores with the agegroup = "Child"

CREATE TABLE lvl3WomenResultsDay1Child(