Constructing XML data
-
Although I have extracted XML data on occassions, I have never constructed more than very basic XML data and am struggling with the task below.
I need to produce an XML record that has a 'header' section, providing interface information for a target system, followed by different nodes dependent upon the data extracted for this.
As a simplified example:
DECLARE @Version INT;DECLARE @Identifier INT;
SET @Version = 123;
SET @Identifier = 456;
CREATE TABLE #XML_Demo(
Att_IDVARCHAR(10),
Att_RefVARCHAR(10),
Att_AnswerVARCHAR(10),
Att_Ref2VARCHAR(10),
Att_SubINT
);
INSERT INTO #XML_Demo(Att_ID, Att_Ref, Att_Answer, Att_Ref2, Att_Sub)
VALUES
('', '', '567', '1.2.3' , NULL),
('', '', '890', '4.5.6' , NULL),
('123', '12', '345', '6.7.8' , 1),
('123', '12', '567', '1.2.3' , 2),
('124', '34', '567', '4.5.6' , 3),
('', '', '567', '1.2.3' , 4),
('', '', '789', '4.5.6' , 5);
select * from #XML_Demo;
I should be able to produce the following XML data:
<?xml version="1.0" encoding="utf-8" ?>- <MainData>
- <MainDataHeader>
<TheVersion>123</TheVersion>
<TheIdentifier>456</TheIdentifier>
</MainDataHeader>
- <NullSubData>
<OtherAnswer NullField1="567" NullField2="1.2.3" />
<OtherAnswer NullField1="890" NullField2="4.5.6" />
</NullSubData>
- <OtherData>
- <Other ID="123" Ref="12">
<OtherAnswer NullField1="345" NullField2="6.7.8" />
<OtherAnswer NullField1="567" NullField2="1.2.3" />
</Other>
- <Other ID="124" Ref="34">
<OtherAnswer NullField1="567" NullField2="4.5.6" />
</Other>
- <Other ID="" Ref="">
<OtherAnswer NullField1="567" NullField2="1.2.3" />
<OtherAnswer NullField1="789" NullField2="4.5.6" />
</Other>
</OtherData>
</MainData>
Where 'Att_Sub' is NULL, the data should be recorded under <NullSubData>, any other value for 'Att_Sub' is grouped under '<OtherData><Other>'.
With my limited XML experience I manage to produce various unsightly versions, but nowhere near what I'm after.
Any assistance with this would be appreciated.
-
You have to use lots of sub queries. The encoding header will have to be manually prepended.
SELECT (SELECT @Version AS "TheVersion",
@Identifier AS "TheIdentifier"
FOR XML PATH('MainDataHeader'),TYPE),
(SELECT Att_Answer AS "@NullField1",
Att_Ref2 AS "@NullField2"
FROM #XML_Demo
WHERE Att_Sub IS NULL
FOR XML PATH('OtherAnswer'),ROOT('NullSubData'),TYPE),
(SELECT t1.Att_ID AS "@ID",
t1.Att_Ref AS "@Ref",
(SELECT t2.Att_Answer AS "@NullField1",
t2.Att_Ref2 AS "@NullField2"
FROM #XML_Demo t2
WHERE t2.Att_ID=t1.Att_ID
AND t2.Att_Ref=t1.Att_Ref
AND t2.Att_Sub IS NOT NULL
ORDER BY t2.Att_Sub
FOR XML PATH('OtherAnswer'),TYPE)
FROM #XML_Demo t1
WHERE t1.Att_Sub IS NOT NULL
GROUP BY t1.Att_ID,t1.Att_Ref
ORDER BY MAX(t1.Att_Sub)
FOR XML PATH('Other'),ROOT('OtherData'),TYPE)
FOR XML PATH('MainData')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Wonderful, thank you very much for your time and assistance.
I had just found the section within my copy of "Programming Microsoft SQL Server 2008", and although I could see it consisted of subqueries I just couldn't get my head around it correctly. I was getting a bit tired of seeing "invalid syntax near ','" and suchlike.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply