SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing from XML to SQL 2014


Importing from XML to SQL 2014

Author
Message
TheFirstOne
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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


Thom A
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96402 Visits: 23423
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Rick-153145
Rick-153145
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2551 Visits: 780
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
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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
sgmunson
SSC Guru
SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)SSC Guru (108K reputation)

Group: General Forum Members
Points: 108705 Visits: 7524
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)
Smile Smile Smile
Health & Nutrition
TheFirstOne
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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
Thom A
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96402 Visits: 23423
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
TheFirstOne
TheFirstOne
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1527 Visits: 294
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.
Attachments
QWomenResults.txt (44 views, 8.00 KB)
QDropTables.txt (40 views, 2.00 KB)
QCreateTables.txt (39 views, 10.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search