Importing from XML to SQL 2014

Question

Importing from XML to SQL 2014

Viewing 15 posts - 31 through 45 (of 128 total)

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 1

TheFirstOne - Thursday, May 17, 2018 4:55 PM
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

Here I can create all tables.
USE meetscoreslive

CREATE TABLE [2WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [3WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [4WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [5WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [6WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [7WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [8WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [9WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE [10WomenResultsDay1]( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE XBWomenResultsDay1( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE XSWomenResultsDay1( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE XGWomenResultsDay1( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

CREATE TABLE XPWomenResultsDay1( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int ); CREATE TABLE XDWomenResultsDay1( ScoreID int, Type varchar(20), CompNum varchar(20), LastName varchar(30), FirstName varchar(20), AG varchar(5), event varchar(3), avgscore varchar(6), bscore varchar(6), Judge1RawScore int, Judge2RawScore int, PlaceOrder int );

TheFirstOne SSCommitted Points: 1659 More actions · Answer 2

Ok, after staring at it for 6 hours I see that you are just dumping the whole xml data line by line into ScoresXML. That is why when I execute this code.
SELECT * INTO [2WomenResultsDay1] FROM #Test;
I get a new table but only ScoresID and ScoresXML for columns and with all the data in one continuous line. You then use the SELECT statement to print out a set of columns and rows.

If I use the other query to make a table how can I use this code to copy the column data?
SELECT * INTO #Test(ScoresXML) FROM XMLImport;

sgmunson SSC Guru Points: 110651 More actions · Answer 3

Okay... clearly I gave you a bunch of new stuff, so let's break it down into pieces that are a little easier to swallow. Having looked at your XML files, I had to try and figure out how the data is related, and as I've not had all that much opportunity to work with XML in a way that gets at the individual elements and values, I had to do a little research myself on how SQL does that kind of XML "shredding". That's the term for reaching into XML data and taking out only the pieces you want. Thus, my query uses the available shredding techniques to do so, based on the structure of the data in the XML. If you look at the results of running the entire query that I posted, you see the data in a way that actually makes more sense than trying to understand in its native XML format.

So to get some of the concepts in the query out of the way, an INNER JOIN is where you take two tables and require that both of them contribute to the result set, and that both tables only contribute rows that meet the JOIN condition, which is in the ON clause. When you use a LEFT OUTER JOIN, the first table shown contributes ALL of its records, while the one following the LEFT OUTER JOIN only contributes those records that meet the JOIN condition (the ON clause). For any row in the first table, the 2nd table will contribute NULL values for any of its columns that appear in the SELECT.

Then there's CROSS APPLY and OUTER APPLY. The word APPLY is referred to as an operator, because it's not quite the same as a JOIN as it has no ON clause, so there's no JOIN condition. It's more of a way to get all the possible combinations of data. and in this case, it was used to get at lower levels of the XML structure. The only difference between a CROSS APPLY and an OUTER APPLY is that in CROSS APPLY, you always have to have a record from the table or subquery specified after APPLY. With OUTER, that's not the case, and similarly to LEFT OUTER JOIN, values in the SELECT that come from the OUTER APPLY table or subquery will be NULL when any correlation between the tables doesn't match up, If there is no correlation, then it won't matter which apply is used.

Now back to the XML. One of the basic techniques of shredding XML is to use the nodes functionality. Since XML is hierarchical in nature, a node is simply a level of the hierarchy. If you look at the XML data, you can see the various levels. Every new tag that appears within a previous tag's scope represents a level. In your XML, the highest level is MeetResultsMsg. Then comes Athlete, then Scores, then Score, then JudgeScore, and moving back up to the same level as Scores, there's the Places level, then Place beneath that. Each of the "elements" that we have an interest in are those parts where you see things like id="1", or bscore="9.500". Since scores and places are at the same level, flattening out that hierarchy to associate the places with the scores means having to separately point to those different levels of the hierarchy. We're simply merging the two slightly different points of view to consolidate the data back to the athlete level. Using CROSS APPLY and OUTER APPLY, we can navigate down the levels by pointing to a sub-level in the APPLY, and retrieve the needed level data. It's somewhat like recursion, but without having to actually use recursion. Think of the APPLYs that are there as inner FOR / NEXT loops in a BASIC program.

Then we use the values functionality to actually get the element values. Think of that as the action that takes place inside of our FOR / NEXT loop. Not sure if you've ever seen the BASIC language (VBScript, VBA, and VB are all derivatives), so I may be using a useless analogy here....

Hopefully, that's not too much to swallow all at once, but if anything I've explained here doesn't make sense, please ask.

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

sgmunson SSC Guru Points: 110651 More actions · Answer 4

TheFirstOne - Thursday, May 17, 2018 6:30 PM
Ok, after staring at it for 6 hours I see that you are just dumping the whole xml data line by line into ScoresXML. That is why when I execute this code.
SELECT * INTO [2WomenResultsDay1] FROM #Test;
I get a new table but only ScoresID and ScoresXML for columns and with all the data in one continuous line. You then use the SELECT statement to print out a set of columns and rows.
If I use the other query to make a table how can I use this code to copy the column data?
SELECT * INTO #Test(ScoresXML) FROM XMLImport;

That isn't going to work. ScoresXML is an XML column. Unless you use a query to get to the nodes and the values within the XML, you are just going to get a copy of the XML. You can't just treat XML as if SQL Server automatically knows what the content is. After all, if you have an XML column in a table, you could put completely differently structured XML in every single row, and then trying to write a query against that table AND get element values out would be a nightmare. What I would want to try to accomplish would be to get each XML file into a single row in the #Test table, but only identically structured XML in any one table. If there's a different XML structure, then you want those files in a separate table.

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 5

I think this one does that?

USE meetscoreslive

CREATE TABLE #XmlData ([FileName] VARCHAR(500))

--plain vanilla dos dir command with /B switch (bare format) INSERT INTO #XmlData EXEC MASTER..XP_CMDSHELL 'dir c:\Proscore5\xml\ /B'

--delete the null values DELETE #XmlData WHERE [FileName] IS NULL

-- Delete all the files that don't have xml extension DELETE #XmlData WHERE [FileName] NOT LIKE '%.xml'

--this will be used to loop over the table alter table #XmlData add id int identity go

select * from #XmlData

CREATE TABLE [dbo].[XMLImport]( [filename] [VARCHAR](500) NULL, [timecreated] [DATETIME] NULL, [xmldata] [xml] NULL ) ON [PRIMARY] GO

truncate table XMLImport --in case you want to rerun just this codeblock declare @Directory varchar(50) select @Directory = 'c:\proscore5\xml\'

declare @FileExist int DECLARE @FileName varchar(500),@DeleteCommand varchar(1000),@FullFileName varchar(500)

DECLARE @SQL NVARCHAR(1000),@xml xml

--This is so that we know how long the loop lasts declare @LoopID int, @MaxID int SELECT @LoopID = min(id),@MaxID = max(ID) FROM #XmlData

WHILE @LoopID <= @MaxID BEGIN

SELECT @FileNAme = filename from #XmlData where id = @LoopID

SELECT @FullFileName = @Directory + @FileName exec xp_fileexist @FullFileName , @FileExist output if @FileExist =1 --sanity check in case some evil person removed the file begin SELECT @SQL = N'select @xml = xml FROM OPENROWSET(BULK ''' + @FullFileName +''' ,Single_BLOB) as TEMP(xml)' -- Just like in the bedroom, this is where the magic happens -- We use the output functionality to fill the xml variable for later use EXEC SP_EXECUTESQL @SQL, N'@xml xml OUTPUT', @xml OUTPUT --The actual insert happens here, as you can see we use the output value (@xml) INSERT XMLImport ([filename],timecreated,xmldata) SELECT @FileName,getdate(),@xml SET @DeleteCommand = 'del ' + @Directory + @FileName --maybe you want to delete or move the file to another directory -- ** here is how to delete the files you just imported -- uncomment line below to delete the file just inserted --EXEC MASTER..XP_CMDSHELL @DeleteCommand -- ** end of here is how to delete the files end

--Get the next id, instead of +1 we grab the next value in case of skipped id values SELECT @LoopID = min(id) FROM #XmlData where id > @LoopID END

select * from XMLImport

IF object_id('tempdb..#XmlData') is not null BEGIN DROP TABLE #XmlData END

TheFirstOne SSCommitted Points: 1659 More actions · Answer 6

TheFirstOne - Saturday, May 19, 2018 10:37 AM
I think this one does that?
USE meetscoreslive
CREATE TABLE #XmlData ([FileName] VARCHAR(500))
--plain vanilla dos dir command with /B switch (bare format) INSERT INTO #XmlData EXEC MASTER..XP_CMDSHELL 'dir c:\Proscore5\xml\ /B'
--delete the null values DELETE #XmlData WHERE [FileName] IS NULL
-- Delete all the files that don't have xml extension DELETE #XmlData WHERE [FileName] NOT LIKE '%.xml'
--this will be used to loop over the table alter table #XmlData add id int identity go
select * from #XmlData
CREATE TABLE [dbo].[XMLImport]( [filename] [VARCHAR](500) NULL, [timecreated] [DATETIME] NULL, [xmldata] [xml] NULL ) ON [PRIMARY] GO
truncate table XMLImport --in case you want to rerun just this codeblock declare @Directory varchar(50) select @Directory = 'c:\proscore5\xml\'
declare @FileExist int DECLARE @FileName varchar(500),@DeleteCommand varchar(1000),@FullFileName varchar(500)
DECLARE @SQL NVARCHAR(1000),@xml xml
--This is so that we know how long the loop lasts declare @LoopID int, @MaxID int SELECT @LoopID = min(id),@MaxID = max(ID) FROM #XmlData
WHILE @LoopID <= @MaxID BEGIN
SELECT @FileNAme = filename from #XmlData where id = @LoopID
SELECT @FullFileName = @Directory + @FileName exec xp_fileexist @FullFileName , @FileExist output if @FileExist =1 --sanity check in case some evil person removed the file begin SELECT @SQL = N'select @xml = xml FROM OPENROWSET(BULK ''' + @FullFileName +''' ,Single_BLOB) as TEMP(xml)' -- Just like in the bedroom, this is where the magic happens -- We use the output functionality to fill the xml variable for later use EXEC SP_EXECUTESQL @SQL, N'@xml xml OUTPUT', @xml OUTPUT --The actual insert happens here, as you can see we use the output value (@xml) INSERT XMLImport ([filename],timecreated,xmldata) SELECT @FileName,getdate(),@xml SET @DeleteCommand = 'del ' + @Directory + @FileName --maybe you want to delete or move the file to another directory -- ** here is how to delete the files you just imported -- uncomment line below to delete the file just inserted --EXEC MASTER..XP_CMDSHELL @DeleteCommand -- ** end of here is how to delete the files end
--Get the next id, instead of +1 we grab the next value in case of skipped id values SELECT @LoopID = min(id) FROM #XmlData where id > @LoopID END
select * from XMLImport
IF object_id('tempdb..#XmlData') is not null BEGIN DROP TABLE #XmlData END

So I am thinking if I read each line in column xmldata into a #temp table I can then use it on the code you wrote? or since there is a temp table #XML Data already I can use that? I will experiment on that.

sgmunson SSC Guru Points: 110651 More actions · Answer 7

You appear to be going down the right road here. If the data in EVERY one of the xml files has the exact same schema, then my code would be able to function, but the question is what differentiates the events in one file from another file. We may have to go get that value from each row's XML in order for the data to actually fully make sense. But do keep going and post back once you've successfully imported all the files into a temp table and then we can go from there.

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 8

I had some time today to look at it again. I came up with this solution to import one file.

use meetscoreslive

DECLARE @Test TABLE (ScoresID int, ScoresXML xml); INSERT INTO @Test(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 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]

SELECT * INTO [2WomenResultsDay1] FROM @Test;

One issue is the file 2WomenResultsDay1 only had two columns with all the data in and not the output of @test-2. I know the @test-2 is not a good way to do it but I got it to work this way. If I can get this to work then all I have to do is read all the files in the folder.

sgmunson SSC Guru Points: 110651 More actions · Answer 9

TheFirstOne - Thursday, May 31, 2018 10:25 PM
I had some time today to look at it again. I came up with this solution to import one file.
use meetscoreslive
DECLARE @Test TABLE (ScoresID int, ScoresXML xml); INSERT INTO @Test(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 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]
SELECT * INTO [2WomenResultsDay1] FROM @Test;
One issue is the file 2WomenResultsDay1 only had two columns with all the data in and not the output of @test-2. I know the @test-2 is not a good way to do it but I got it to work this way. If I can get this to work then all I have to do is read all the files in the folder.

Well, if you took the "INTO [2WomenResultsDay1]" and moved it immediately following the SELECT statement that follows your last CTE, then you'd get the data that is selected into table form, which would be more valuable. There's little point in using SELECT on the @test-2 table without using the CTEs. But, once you then effectively use that SELECT from the CTEs as a way to create the table, then you've got gold.

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 10

sgmunson - Friday, June 1, 2018 6:58 AM
TheFirstOne - Thursday, May 31, 2018 10:25 PM
I had some time today to look at it again. I came up with this solution to import one file.
use meetscoreslive
DECLARE @Test TABLE (ScoresID int, ScoresXML xml); INSERT INTO @Test(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 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]
SELECT * INTO [2WomenResultsDay1] FROM @Test;
One issue is the file 2WomenResultsDay1 only had two columns with all the data in and not the output of @test-2. I know the @test-2 is not a good way to do it but I got it to work this way. If I can get this to work then all I have to do is read all the files in the folder.
Well, if you took the "INTO [2WomenResultsDay1]" and moved it immediately following the SELECT statement that follows your last CTE, then you'd get the data that is selected into table form, which would be more valuable. There's little point in using SELECT on the @test-2 table without using the CTEs. But, once you then effectively use that SELECT from the CTEs as a way to create the table, then you've got gold.

Got it 🙂

TheFirstOne SSCommitted Points: 1659 More actions · Answer 11

In order to seperate the age groups and events I was repeating the SELECT statement but I had to restate the WITH statements. I thought since the data is in memory I could just repease e SELECT statements to create different tables. Also the sorting statement is not working.
ORDER BY avgscore DESC;

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

---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 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 lvl2AgJra

FROM (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]

ORDER BY avgscore DESC;

sgmunson SSC Guru Points: 110651 More actions · Answer 12

TheFirstOne - Friday, June 1, 2018 10:56 AM
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]
So now I should be able to make multiple tables sorting on Age Group and Scores?

What happened to your CTEs ? Those have to stick around for the insert as the basis for the rest of the query. In any case, assuming that all the files have exactly the same structure, then they could all be inserted into that table following the first one that creates the table., Then you could query for any given event from the table with the useful columns. That was what I was shooting for. The idea is to be event agnostic from the point of view of that new table. Therefore, don't name it by a specific event. Just name it something like EventResults, or something of that nature.

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

TheFirstOne SSCommitted Points: 1659 More actions · Answer 13

sgmunson - Friday, June 1, 2018 11:39 AM
TheFirstOne - Friday, June 1, 2018 10:56 AM
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]
So now I should be able to make multiple tables sorting on Age Group and Scores?
What happened to your CTEs ? Those have to stick around for the insert as the basis for the rest of the query. In any case, assuming that all the files have exactly the same structure, then they could all be inserted into that table following the first one that creates the table., Then you could query for any given event from the table with the useful columns. That was what I was shooting for. The idea is to be event agnostic from the point of view of that new table. Therefore, don't name it by a specific event. Just name it something like EventResults, or something of that nature.

I am having trouble understanding the CTEs

TheFirstOne SSCommitted Points: 1659 More actions · Answer 14

TheFirstOne - Friday, June 1, 2018 12:16 PM
sgmunson - Friday, June 1, 2018 11:39 AM
TheFirstOne - Friday, June 1, 2018 10:56 AM
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]
So now I should be able to make multiple tables sorting on Age Group and Scores?
What happened to your CTEs ? Those have to stick around for the insert as the basis for the rest of the query. In any case, assuming that all the files have exactly the same structure, then they could all be inserted into that table following the first one that creates the table., Then you could query for any given event from the table with the useful columns. That was what I was shooting for. The idea is to be event agnostic from the point of view of that new table. Therefore, don't name it by a specific event. Just name it something like EventResults, or something of that nature.
I am having trouble understanding the CTEs

I changed my previous post to reflect separating by age group

TheFirstOne SSCommitted Points: 1659 More actions · Answer 15

TheFirstOne - Friday, June 1, 2018 12:18 PM
TheFirstOne - Friday, June 1, 2018 12:16 PM
sgmunson - Friday, June 1, 2018 11:39 AM
TheFirstOne - Friday, June 1, 2018 10:56 AM
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]
So now I should be able to make multiple tables sorting on Age Group and Scores?
What happened to your CTEs ? Those have to stick around for the insert as the basis for the rest of the query. In any case, assuming that all the files have exactly the same structure, then they could all be inserted into that table following the first one that creates the table., Then you could query for any given event from the table with the useful columns. That was what I was shooting for. The idea is to be event agnostic from the point of view of that new table. Therefore, don't name it by a specific event. Just name it something like EventResults, or something of that nature.
I am having trouble understanding the CTEs
I changed my previous post to reflect separating by age group

Yes all files have the same structure