December 19, 2017 at 7:31 am
Hi All,
Using XML and T-SQL is not my strongest point. At the moment, I'm looking to try and create some XML, that nests based on the value of the column.
So, the sample data looks like this:CREATE TABLE #Sample (Frame varchar(4),
PropertyNumber int,
Idat datetime);
INSERT INTO #Sample
VALUES ('M064',1,'20130814'),('M065',1,'20130814'),('Z999',1,'20130814'),('Z999',1,'20130814'),('M031',2,'20130814'),('Z999',2,'20130814'),('Z999',2,'20130814'),('M064',3,'20130814'),('M065',3,'20130814'),('Z999',3,'20130814');
GO
--DROP TABLE #Sample;
GO
The XML I'm looking to get out would be:
I'm aware, I could do this by declaring separate "nests" for each frame, however, there are 87 different frames (this is just a small data set) thus I'd rather avoid having to do so.
Effectively I'm trying to do something like this (obviously this isn't valid syntax):SELECT CONVERT(int,PropertyNumber) AS Prn,
CONVERT(int,PropertyNumber) AS Prnnumber,
CONVERT(int,PropertyNumber) AS num,
CONVERT(varchar(10),idat, 103) AS Dateon
FROM #Sample S
FOR XML PATH (S.Frame);
Any one able to advise?
Thanks all!
Edit: Heh, funny, you can spend 2 hours trying to figure something out, then after you post, have a brainwave.
This seems to do the job, however, performance is awful on the live data (rather than the small sample), so if someone has a better answer, please do share.
SELECT CONVERT(xml,REPLACE(REPLACE(CONVERT(varchar(max),(
SELECT CONVERT(int,PropertyNumber) AS Prn,
CONVERT(int,PropertyNumber) AS Prnnumber,
CONVERT(int,PropertyNumber) AS num,
CONVERT(varchar(10),idat, 103) AS Dateon
FROM #Sample Sq
WHERE Sq.Frame = S.Frame
AND Sq.PropertyNumber = S.PropertyNumber
FOR XML PATH('td'))),'<td>', '<' + S.Frame + '>'),'</td>', '</' + S.Frame + '>'))
FROM #Sample S
FOR XML PATH ('');
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 19, 2017 at 8:10 am
Thom,
Unless I am mis-reading your problem, the frame code is an attribute not a tag as all of the sub-tags have the same structure and your file should look like this
<frames>
<frame attribute = "M064">
<Prn>1</Prn>
<Prnnumber>1</Prnnumber>
<Num>1</num>
<Dateon>14/08/2013</Dateon>
</frame>
<frame attribute = "M065>
...
</frame>
....
</frames>
or you could do it as <frame><name>M064</name>...</frame>
as they are a collection they should be included in a <frames> collection tag - valid XML can only have one root node
If you want each one to have a separate tag then I think you will have no choice but to nest for FOR XML queries.
From experience, don't try and do this against in-memory tables - especially CTEs - write the data back to the disk ats a temp table - I have seen in excess of a 10x improvement as a result (but my servers may just be under memory pressure)
I've just done this for real - 20K letters with data nested up to 4 levels deep takes about 6 minutes to produce 150K lines of nested XML so perfomrance is not too bad.
Good Luck
December 19, 2017 at 8:19 am
Hi Aaron, an attribute won't work, as the xml is fed to an application, which expects the format to be a certain way (that above). Using attributes is not the correct answer I'm afraid.
The sample above is actually apart of a much larger xml file, but this is the bit I'm stuck on. I could code these all manually, however, that'll likely take me the best of the rest of the week.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 19, 2017 at 8:36 am
Thinking outside the box, could you write a cursor that prints out the SQL for the nested FOR XML and then copy-paste that into your logic
Code that writes code that writes itself - next stop SKYNET 🙂
December 19, 2017 at 8:39 am
aaron.reese - Tuesday, December 19, 2017 8:36 AMThinking outside the box, could you write a cursor that prints out the SQL for the nested FOR XML and then copy-paste that into your logicCode that writes code that writes itself - next stop SKYNET 🙂
Cursor is definitely not an option.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 19, 2017 at 8:55 am
not a cursor to run the extract, a cursor to generate the SQL code for the nested XML
declare @tbl as table
(
nodename nvarchar(10)
)
insert into @tbl (nodename) values ('M064')
insert into @tbl (nodename) values ('M065')
insert into @tbl (nodename) values ('Z999')
declare @nodename nvarchar(10)
declare @SQL nvarchar(max)
declare csr cursor for SELECT nodename from @tbl
open csr
fetch csr into @nodename
while @@FETCH_STATUS = 0
BEGIN
set @SQL = 'SELECT CONVERT(int,PropertyNumber) AS Prn, CONVERT(int,PropertyNumber) AS Prnnumber, CONVERT(int,PropertyNumber) AS num, CONVERT(varchar(10),idat, 103) AS Dateon FROM #Sample S FOR XML PATH (''' + @nodename + ''')';
print @SQL
fetch csr into @nodename
END
close csr
deallocate csr
gives
SELECT CONVERT(int,PropertyNumber) AS Prn, CONVERT(int,PropertyNumber) AS Prnnumber, CONVERT(int,PropertyNumber) AS num, CONVERT(varchar(10),idat, 103) AS Dateon FROM #Sample S FOR XML PATH ('M064')
SELECT CONVERT(int,PropertyNumber) AS Prn, CONVERT(int,PropertyNumber) AS Prnnumber, CONVERT(int,PropertyNumber) AS num, CONVERT(varchar(10),idat, 103) AS Dateon FROM #Sample S FOR XML PATH ('M065')
SELECT CONVERT(int,PropertyNumber) AS Prn, CONVERT(int,PropertyNumber) AS Prnnumber, CONVERT(int,PropertyNumber) AS num, CONVERT(varchar(10),idat, 103) AS Dateon FROM #Sample S FOR XML PATH ('Z999')
December 19, 2017 at 9:23 am
OK, got it. My initial solution worked, but because the original data I had was also coming from XML, it was just overkill for the server. Instead, I put all the data extracted from the original xml into a staging table, then used the staging table's data instead. This worked fine, and performance was pretty good (takes about 4 seconds to do the full xml generation (which is not a small file).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 20, 2017 at 6:38 am
You'll either need a dynamic query or if the number of distinct values are limited, use a case statement.
😎
CREATE TABLE #Sample (RID INT IDENTITY(1,1) NOT NULL,
Frame varchar(4),
PropertyNumber int,
Idat datetime);
INSERT INTO #Sample
VALUES ('M064',1,'20130814'),('M065',1,'20130814'),('Z999',1,'20130814'),('Z999',1,'20130814'),('M031',2,'20130814'),('Z999',2,'20130814'),('Z999',2,'20130814'),('M064',3,'20130814'),('M065',3,'20130814'),('Z999',3,'20130814');
GO
SELECT
CASE WHEN S.Frame = 'M064' THEN ( SELECT
S.PropertyNumber AS Prn
,S.PropertyNumber AS Prnnumber
,S.Idat AS Dateon
FROM #Sample SS
WHERE S.RID = SS.RID
FOR XML PATH(''),TYPE) END AS M064
,CASE WHEN S.Frame = 'M065' THEN ( SELECT
S.PropertyNumber AS Prn
,S.PropertyNumber AS Prnnumber
,S.Idat AS Dateon
FROM #Sample SS
WHERE S.RID = SS.RID
FOR XML PATH(''),TYPE) END AS M065
,CASE WHEN S.Frame = 'Z999' THEN ( SELECT
S.PropertyNumber AS Prn
,S.PropertyNumber AS Prnnumber
,S.Idat AS Dateon
FROM #Sample SS
WHERE S.RID = SS.RID
FOR XML PATH(''),TYPE) END AS Z999
,CASE WHEN S.Frame = 'M031' THEN ( SELECT
S.PropertyNumber AS Prn
,S.PropertyNumber AS Prnnumber
,S.Idat AS Dateon
FROM #Sample SS
WHERE S.RID = SS.RID
FOR XML PATH(''),TYPE) END AS M031
FROM #Sample S
FOR XML PATH(''), TYPE
DROP TABLE #Sample;
GO
Output<M064>
<Prn>1</Prn>
<Prnnumber>1</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</M064>
<M065>
<Prn>1</Prn>
<Prnnumber>1</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</M065>
<Z999>
<Prn>1</Prn>
<Prnnumber>1</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</Z999>
<Z999>
<Prn>1</Prn>
<Prnnumber>1</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</Z999>
<M031>
<Prn>2</Prn>
<Prnnumber>2</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</M031>
<Z999>
<Prn>2</Prn>
<Prnnumber>2</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</Z999>
<Z999>
<Prn>2</Prn>
<Prnnumber>2</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</Z999>
<M064>
<Prn>3</Prn>
<Prnnumber>3</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</M064>
<M065>
<Prn>3</Prn>
<Prnnumber>3</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</M065>
<Z999>
<Prn>3</Prn>
<Prnnumber>3</Prnnumber>
<Dateon>2013-08-14T00:00:00</Dateon>
</Z999>
December 20, 2017 at 6:42 am
Thanks Eirikur, but that was the method I was trying to avoid, due to there being over 85 variations of the frame in the full data. The REPLACE method posted in my initial post's edit does seem to work very well, without the use of dynamic SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 20, 2017 at 7:50 am
Thom A - Wednesday, December 20, 2017 6:42 AMThanks Eirikur, but that was the method I was trying to avoid, due to there being over 85 variations of the frame in the full data. The REPLACE method posted in my initial post's edit does seem to work very well, without the use of dynamic SQL.
You are welcome Tom, my guess is that the dynamic query will be faster as there will only be one XML construct per unique value in the query which is the most expensive part of it, further, using a simple CTE for the distinct values makes generating the dynamic code absolutely straight forward. The type conversion, string replacement and concatenation also come at an additional cost on top.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply