Importing from XML to SQL 2014
- TheFirstOne - Friday, June 1, 2018 12:20 PM
Yes I am hoping to import all the xml data into one file. I am just using the test.xml to test the program. My hope is to read all the xml into @test-2 and then separate into tables by agegroup. I will then need to sort by avgscore.
- TheFirstOne - Friday, June 1, 2018 12:39 PM
Or I could have 14 separate query's on the 14 xml files which will separate all the age groups and scores and then have master query that executes all 14 sub query every 5 or 10 sec?
- TheFirstOne - Friday, June 1, 2018 12:43 PM
What kind of output are you expecting? Is it 14 different reports? What kind of display of this information did you have in mind? There's no reason not to have just one table instead of 14, because once the data from the XML files is imported into that single table, the report queries should be trivial and run so fast you almost can't measure the elapsed time.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Having one table would be the best but one of the issues we have is the place order is not correct. Since the software does not include the age groups the place order is across all age groups. This will not be fixable, all they can do is add the age group to the xml file. So I need to import the data either by individual file or merging the xml data and then separate it into level-event-agegroup. We will then output the file in the correct order.
Level 2
AG - JRA
Event -1
name, score 9.1
name, score 8.7
name, score 8.0Level 2
AG - JRA
Event -2
name, score 9.1
name, score 8.7
name, score 8.0and so on down the line. I know it is not perfect.
-
I added this sorting function and it is working but it seams to be sorting on some other value.
GROUP BY CS1.ScoresID, CS1.[Type], CS1.AG, CS1.CompNum, CS1.LastName, CS1.FirstName,
CS1.[event], CS1.avgscore, CS1.bscore, CS1.rawscore, CS2.rawscore,
CP.PlaceOrder
ORDER BY CS1.avgscore,CS1.LastName DESC; - TheFirstOne - Friday, June 1, 2018 1:05 PM
If the data isn't representative of the group it's contained in, then new values will have to be computed in the query or queries. Because you have the grouping information in every row, that should make it possible to use either ROW_NUMBER() or DENSE_RANK() and a PARTITION BY within the OVER clause to compute placement.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Friday, June 1, 2018 1:25 PM
is it not sorting because the ScoresID is NULL?
-
So I was looking at the PARTITION BY here https://stackoverflow.com/questions/9315070/trying-to-understand-over-and-partition-by. So I need to fix it so the ScoresID is a valid number and then do the sorting. The one issue I am having is I am failing at importing all xml files into one temp table so I may need to have separate query's to get the results.
- TheFirstOne - Friday, June 1, 2018 1:34 PM
What is ScoresID doing. I deleted it and it still works?
-
Ok, so the summer is finally over and everyone is back to school and I can devote more time to the script. I have been going over everything and I think it would be best to import the data from all 10 xml documents and then split them up into multiple tables. The current script works for the first table but the values must be dropping (CompScores) after the first SELECT INTO because when I add a second one I get the error.
(1 row(s) affected)(240 row(s) affected)
(9 row(s) affected)
Msg 208, Level 16, State 1, Line 124
Invalid object name 'CompScores'.I am including the whole script
use meetscoresliveDECLARE @test-2 TABLE (ScoresID int, ScoresXML xml);
INSERT INTO @test-2(ScoresXML)
SELECT * FROM OPENROWSET(
BULK 'C:\Proscore5\test.xml',
SINGLE_BLOB) AS x;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-2 AS t
CROSS APPLY t.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') c(p)
OUTER APPLY c.p.nodes('./Places/Place') pl(p)
)---All Data For Level 2---
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
INTO [2WomenResultsDay1]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]ORDER BY LastName;
---Level 2 Age Group JRA---
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-2 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
INTO lvl2AgJraFROM (SELECT * FROM CompScores WHERE JudgeID = '1' AND AG = 'JRA' AND [event] = '1') AS CS1
INNER JOIN (SELECT * FROM CompScores WHERE JudgeID = '2' AND AG = 'JRA' AND [event] = '1') 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]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.PlaceOrderINTO lvl2AgJrb
FROM (SELECT * FROM CompScores WHERE JudgeID = '1' AND AG = 'JRB' AND [event] = '1') AS CS1
INNER JOIN (SELECT * FROM CompScores WHERE JudgeID = '2' AND AG = 'JRB' AND [event] = '1') 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]ORDER BY avgscore
- CTEs are only valid for a single query, you'd have to repeat it for every single query. But I really wouldn't do that. Apart from being difficult to maintain just for creating the data, putting everything into separate tables will make reporting on it an absolute nightmare - you'll end up writing the same code over and over with a different table name, instead of just specifying a where clause with suitable parameters.
Looking at your XML structure, I'd go with three tables: The first to store Athlete details, a second for the Scores and finally one for the Places. Then you can just join these as the basis for pretty much every query you could make over the XML.
For example (note I've just used VC(100) columns everywhere for expedience, you should use sensible types instead!)
Drop Table If Exists #Athletes
Drop Table If Exists #Scores
Drop Table If Exists #Places
Go
DECLARE @test-2 TABLE (ScoresID int, ScoresXML xml);
INSERT INTO @test-2(ScoresXML)
SELECT * FROM OPENROWSET(
BULK 'C:\test\test_backup.xml',
SINGLE_BLOB) AS x;-- Athletes
Select
M.A.value('@Type','varchar(100)') As [Type],
M.A.value('@CompNum','varchar(100)') As CompNum,
M.A.value('@LastName','varchar(100)') As LastName,
M.A.value('@FirstName','varchar(100)') As FirstName,
M.A.value('@AG','varchar(100)') As AG
Into #Athletes
From @test-2 T
Cross Apply T.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') M(A)-- Scores
Select
M.A.value('@CompNum','varchar(100)') As CompNum,
SC.S.value('@event', 'varchar(100)') As [Event],
SC.S.value('@avgscore', 'varchar(100)') As AvgScore,
SC.S.value('@bscore', 'varchar(100)') As BScore,
JS1.S.value('@score', 'varchar(100)') As Judge1Score,
JS2.S.value('@score', 'varchar(100)') As Judge2Score
Into #Scores
From @test-2 T
Cross Apply T.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') M(A)
Cross Apply M.A.nodes('Scores/Score') SC(S)
Outer Apply SC.S.nodes('JudgeScore[@id=1]') JS1(S)
Outer Apply SC.S.nodes('JudgeScore[@id=2]') JS2(S)-- Places
Select
M.A.value('@CompNum','varchar(100)') As CompNum,
PL.P.value('@event', 'varchar(100)') As [Event],
PL.P.value('@rank', 'varchar(100)') As [Rank],
PL.P.value('@order', 'varchar(100)') As [Order]
Into #Places
From @test-2 T
Cross Apply T.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') M(A)
Cross Apply M.A.nodes('Places/Place') PL(P)-- Fully denormalised results
Select *
From #Athletes A
Join #Places P On A.CompNum = P.CompNum
Join #Scores S On P.Event = S.Event And S.CompNum = A.CompNum -
Ok, I took a look at the code and made some modifications and had some questions.
Here I needed to make a change for backwards compatibility
-- Temp tables for example, these would be real tables you INSERT INTO
IF OBJECT_ID('tempdb..#Athletes') IS NOT NULL
DROP TABLE #Athletes;
GO
IF OBJECT_ID('tempdb..#Scores') IS NOT NULL
DROP TABLE #Scores;
GO
IF OBJECT_ID('tempdb..#Places') IS NOT NULL
DROP TABLE #Places;
GO
IF OBJECT_ID('tempdb..#Event_Total') IS NOT NULL
DROP TABLE #Event_Total;
GOI also created another table because I need all the data in one file so I can output the results for our App to grab. The core info is Name, Age Group, Event, Judges Score.
-- Event_Total
Select
M.A.value('@Type','varchar(100)') As [Type],
M.A.value('@CompNum','varchar(100)') As CompNum,
M.A.value('@LastName','varchar(100)') As LastName,
M.A.value('@FirstName','varchar(100)') As FirstName,
M.A.value('@AG','varchar(100)') As AG,
PL.P.value('@event', 'varchar(100)') As [Event],
PL.P.value('@rank', 'varchar(100)') As [Rank],
PL.P.value('@order', 'varchar(100)') As [Order],
SC.S.value('@avgscore', 'varchar(100)') As AvgScore,
SC.S.value('@bscore', 'varchar(100)') As BScore,
JS1.S.value('@score', 'varchar(100)') As Judge1Score,
JS2.S.value('@score', 'varchar(100)') As Judge2Score
Into #Event_Total
From @test-2 T
Cross Apply T.ScoresXML.nodes('/MeetResultsMsg/AthleteResults/Athlete') M(A)
Cross Apply M.A.nodes('Scores/Score') SC(S)
Cross Apply M.A.nodes('Places/Place') PL(P)
Outer Apply SC.S.nodes('JudgeScore[@id=1]') JS1(S)
Outer Apply SC.S.nodes('JudgeScore[@id=2]') JS2(S)My question is our App will need to grab only certain information, so for instance all athletes in the JRA group, event 1-4 and AA and sorted by score. Will I be able to grab the information from the Event_Total output or will I need to separate them into individual files?
On another note I am trying to merge all xml files together into one large file. I can do this with a command line (copy *.xml new.xml) but it puts the xml header line throughout the new file and the import fails.
Thank you for the help.
Tim -
also for some reason it is not sorting.
SELECT *
INTO dbo.Event_Total
FROM dbo.#Event_Total
ORDER BY CompNum -
So I upgraded to 17 express so I can get the JSON output function. I then found a program that will create an app from the JSON output (Jasonette http://web.jasonette.com/) I really like the simplicity of this. The only issue I have is the JSON output has CTRL breaks in it and I cannot figure out how to removed them in SQL.
I also need to send the output to a file also but cannot find the answer
Thank you
Tim -
You can use:
REPLACE(YourJSONColumnOrExpression, CHAR(13)+CHAR(10), ;'')Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 46 through 60 (of 127 total)
You must be logged in to reply to this topic. Login to reply