Need help putting data into XML format
-
Hi All
XML has never been a strong point of mine. Need some help getting data into XML format as shown in the last code block.
The datatypes and table structures are pretty much fixed, but I can re-hash data into another Temp Table, CTE, etc...
This is a server running SQL Server 2012, but I'd guess any version that understands FOR XML PATH should be fine.
Let me know if anything else is needed.
Source tables and data
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)
,[Attr3] varchar(2)
,[Attr4] varchar(5)
,[Attr5] integer
,[Attr6] varchar(1)
);
INSERT INTO #Element1
VALUES ('MONTH','2015','07','NEW',39,'Y');
if object_id('Tempdb..#Element2') is not null drop table #Element2;
create table #Element2 (
[Attr5] integer
,[Attr7] varchar(15)
,[Attr8] varchar(100)
,[Attr9] decimal
,[Attr10] decimal
,[Attr11] decimal
);
INSERT INTO #Element2
VALUES (39,'12345','My Reporting Group 1',41297,40616,681)
,(39,'67890','My Reporting Group 2',7372,3552,3820);
if object_id('Tempdb..#Element3') is not null drop table #Element3;
create table #Element3 (
[Attr7] varchar(15)
,[Element4] varchar(50)
,[Element5] decimal
,[Element6] decimal
,[Element7] decimal
,[Element8] decimal
);
INSERT INTO #Element3
VALUES('12345','SubGroup1',75959,209,1,0)
,('12345','SubGroup2',129687,283,1,0)
,('67890','SubGroup1',416931,498,4,0)
,('67890','SubGroup2',66720,82,1,0);
My SQL statement so far
SELECT
e1.[Attr1] [@Attr1]
,e1.[Attr2] as [@Attr2]
,e1.[Attr3] as [@Attr3]
,e1.[Attr4] as [@Attr4]
,e1.[Attr5] as [@Attr5]
,e1.[Attr6] as [@Attr6]
,(
SELECT
e2a.[Attr7] as [@Atrr7]
,e2a.[Attr8] as [@Atrr8]
,e2a.[Attr9] as [@Attr09]
,e2a.[Attr10] as [@Attr10]
,e2a.[Attr11] as [@Attr11]
,(
select
e3.[Element4]
,e3.[Element5]
,e3.[Element6]
,e3.[Element7]
,e3.[Element8]
from #Element2 e2_2
join #Element3 e3 on e3.[Attr7] = e2_2.[Attr7]
where e2a.[Attr5] = e2_2.[Attr5]
and e2a.[Attr7] = e2_2.[Attr7]
for xml path('Element3'), TYPE
) as Element3
from #Element2 e2a
where e1.[Attr5] = e2a.[Attr5]
for xml path('Element2'), TYPE
) as Element2
from #Element1 e1
for xml path('Element1')
Incorrect XML produced from SQL Statement
<Element1 Attr1="MONTH" Attr2="2015" Attr3="07" Attr4="NEW" Attr5="39" Attr6="Y">
<Element2>
<Element2 Atrr7="12345" Atrr8="My Reporting Group 1" Attr09="41297" Attr10="40616" Attr11="681">
<Element3>
<Element3>
<Element4>SubGroup1</Element4>
<Element5>75959</Element5>
<Element6>209</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</Element3>
<Element3>
<Element4>SubGroup2</Element4>
<Element5>129687</Element5>
<Element6>283</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</Element3>
</Element3>
</Element2>
<Element2 Atrr7="67890" Atrr8="My Reporting Group 2" Attr09="7372" Attr10="3552" Attr11="3820">
<Element3>
<Element3>
<Element4>SubGroup1</Element4>
<Element5>416931</Element5>
<Element6>498</Element6>
<Element7>4</Element7>
<Element8>0</Element8>
</Element3>
<Element3>
<Element4>SubGroup2</Element4>
<Element5>66720</Element5>
<Element6>82</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</Element3>
</Element3>
</Element2>
</Element2>
</Element1>
Desired XML output
<Element1 Attr1="MONTH" Attr2="2013" Attr3="07" Attr4="new" Attr5="39" Attr6="Y">
<Element2 Atrr7="201002" Atrr8="My Reporting Group 1" Atrr9="41297" Atrr10="40616" Atrr11="681">
<Element3>
<SubGroup1>
<Element5>75959</Element5>
<Element6>209</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup1>
<SubGroup2>
<Element5>129687</Element5>
<Element6>283</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup2>
</Element3>
</Element2>
<Element2 Atrr7="201338" Atrr7="My Reporting Group 2" Atrr9="7372" Atrr10="3552" Atrr11="3820">
<Element3>
<SubGroup1>
<Element5>416931</Element5>
<Element6>498</Element6>
<Element7>4</Element7>
<Element8>0</Element8>
</SubGroup1>
<SubGroup2>
<Element5>66720</Element5>
<Element6>82</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup2>
</Element3>
</Element2>
</Element1>
--------------------
Colt 45 - the original point and click interface
-
Quick solution
😎
USE tempdb;GO
SET NOCOUNT ON;
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)
,[Attr3] varchar(2)
,[Attr4] varchar(5)
,[Attr5] integer
,[Attr6] varchar(1)
);
INSERT INTO #Element1
VALUES ('MONTH','2015','07','NEW',39,'Y');
if object_id('Tempdb..#Element2') is not null drop table #Element2;
create table #Element2 (
[Attr5] integer
,[Attr7] varchar(15)
,[Attr8] varchar(100)
,[Attr9] decimal
,[Attr10] decimal
,[Attr11] decimal
);
INSERT INTO #Element2
VALUES (39,'12345','My Reporting Group 1',41297,40616,681)
,(39,'67890','My Reporting Group 2',7372,3552,3820);
if object_id('Tempdb..#Element3') is not null drop table #Element3;
create table #Element3 (
[Attr7] varchar(15)
,[Element4] varchar(50)
,[Element5] decimal
,[Element6] decimal
,[Element7] decimal
,[Element8] decimal
);
INSERT INTO #Element3
VALUES('12345','SubGroup1',75959,209,1,0)
,('12345','SubGroup2',129687,283,1,0)
,('67890','SubGroup1',416931,498,4,0)
,('67890','SubGroup2',66720,82,1,0);
SELECT
EL1.Attr1 AS '@Attr1'
,EL1.Attr2 AS '@Attr2'
,EL1.Attr3 AS '@Attr3'
,EL1.Attr4 AS '@Attr4'
,EL1.Attr5 AS '@Attr5'
,EL1.Attr6 AS '@Attr6'
,(
SELECT
EL2.Attr7 AS 'Element2/@Attr7'
,EL2.Attr8 AS 'Element2/@Attr8'
,EL2.Attr9 AS 'Element2/@Attr9'
,EL2.Attr10 AS 'Element2/@Attr10'
,EL2.Attr11 AS 'Element2/@Attr11'
,(
SELECT
X.[SubGroup1/Element5]
,X.[SubGroup1/Element6]
,X.[SubGroup1/Element7]
,X.[SubGroup1/Element8]
FROM (
SELECT
EL3.Element5 AS 'SubGroup1/Element5'
,EL3.Element6 AS 'SubGroup1/Element6'
,EL3.Element7 AS 'SubGroup1/Element7'
,EL3.Element8 AS 'SubGroup1/Element8'
FROM #Element3 EL3
WHERE EL3.Attr7 = EL2.Attr7
AND EL3.Element4 = 'SubGroup1'
UNION ALL
SELECT
EL3.Element5 AS 'SubGroup2/Element5'
,EL3.Element6 AS 'SubGroup2/Element6'
,EL3.Element7 AS 'SubGroup2/Element7'
,EL3.Element8 AS 'SubGroup2/Element8'
FROM #Element3 EL3
WHERE EL3.Attr7 = EL2.Attr7
AND EL3.Element4 = 'SubGroup2'
) AS X
FOR XML PATH(''), TYPE
) AS 'Element3'
FROM #Element2 EL2
FOR XML PATH(''), TYPE
)
FROM #Element1 EL1
FOR XML PATH('Element1'), TYPE;
Results
<Element1 Attr1="MONTH" Attr2="2015" Attr3="07" Attr4="NEW" Attr5="39" Attr6="Y"><Element2 Attr7="12345" Attr8="My Reporting Group 1" Attr9="41297" Attr10="40616" Attr11="681" />
<Element3>
<SubGroup1>
<Element5>75959</Element5>
<Element6>209</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup1>
<SubGroup1>
<Element5>129687</Element5>
<Element6>283</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup1>
</Element3>
<Element2 Attr7="67890" Attr8="My Reporting Group 2" Attr9="7372" Attr10="3552" Attr11="3820" />
<Element3>
<SubGroup1>
<Element5>416931</Element5>
<Element6>498</Element6>
<Element7>4</Element7>
<Element8>0</Element8>
</SubGroup1>
<SubGroup1>
<Element5>66720</Element5>
<Element6>82</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup1>
</Element3>
</Element1>
-
You may wish to add root element to keep output well-formed xml document when Element1 table has more rows . And subgroups naming may be rendered with attribute which may be easier to parse later.
SELECTe1.[Attr1] [element1/@Attr1]
,e1.[Attr2] as [element1/@Attr2]
,e1.[Attr3] as [element1/@Attr3]
,e1.[Attr4] as [element1/@Attr4]
,e1.[Attr5] as [element1/@Attr5]
,e1.[Attr6] as [element1/@Attr6]
,element1 = (SELECT
e2a.[Attr7] as [@Atrr7]
,e2a.[Attr8] as [@Atrr8]
,e2a.[Attr9] as [@Attr09]
,e2a.[Attr10] as [@Attr10]
,e2a.[Attr11] as [@Attr11]
,element3 = (SELECT
e3.[Element4] as [@name]
,e3.[Element5]
,e3.[Element6]
,e3.[Element7]
,e3.[Element8]
FROM #Element2 e2_2
join #Element3 e3 ON e3.[Attr7] = e2_2.[Attr7]
WHERE e2a.[Attr5] = e2_2.[Attr5]
and e2a.[Attr7] = e2_2.[Attr7]
FOR XML PATH('Element4'), TYPE
)
FROM #Element2 e2a
WHERE e1.[Attr5] = e2a.[Attr5]
FOR XML PATH('element2'), TYPE
)
FROM #Element1 e1
FOR XML PATH(''), ROOT('r')
P.S. root.
-
Your original query was very close. You're getting Element2 and Element3 twice, because you're specifying them both twice: once in the path() and once in the alias. Instead of giving your subqueries aliases of the elements, you need to give them the alias [*].
SELECT
e1.[Attr1] [@Attr1]
,e1.[Attr2] as [@Attr2]
,e1.[Attr3] as [@Attr3]
,e1.[Attr4] as [@Attr4]
,e1.[Attr5] as [@Attr5]
,e1.[Attr6] as [@Attr6]
,(
SELECT
e2a.[Attr7] as [@Atrr7]
,e2a.[Attr8] as [@Atrr8]
,e2a.[Attr9] as [@Attr09]
,e2a.[Attr10] as [@Attr10]
,e2a.[Attr11] as [@Attr11]
,(
select
e3.[Element4]
,e3.[Element5]
,e3.[Element6]
,e3.[Element7]
,e3.[Element8]
from #Element2 e2_2
join #Element3 e3 on e3.[Attr7] = e2_2.[Attr7]
where e2a.[Attr5] = e2_2.[Attr5]
and e2a.[Attr7] = e2_2.[Attr7]
for xml path('Element3'), TYPE
) as [*]
from #Element2 e2a
where e1.[Attr5] = e2a.[Attr5]
for xml path('Element2'), TYPE
) as [*]
from #Element1 e1
for xml path('Element1')
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thanks for all the responses so far. Unfortunately all of them don't quite get there :crying:
The tripping point seems to be the [Element4] values. As shown in the desired output, the value of the sub-type needs to be the element name, not an attribute of that element.
What I've provided is a cut-down list of data. There are 203 possible sub-groups with one or many various sub-groups assigned to each [Attr7] value.
This isn't really feasible given the varying quantity and value of sub-groups
EL3.Element5 AS 'SubGroup1/Element5'This has the value as an attribute instead of an element
<Element4 name="SubGroup1">This has the wrong element name
<Element4>SubGroup1</Element4>For the purposes of this post it may be better if my #Element3 insert was re-written like
INSERT INTO #Element3VALUES('12345','SubGroup1',75959,209,1,0)
,('12345','SubGroup4',129687,283,1,0)
,('12345','SubGroup5',223157,343,1,0)
,('67890','SubGroup2',416931,498,4,0)
,('67890','SubGroup3',66720,82,1,0);
And the desired XML output would then be
<Element1 Attr1="MONTH" Attr2="2013" Attr3="07" Attr4="new" Attr5="39" Attr6="Y"><Element2 Atrr7="201002" Atrr8="My Reporting Group 1" Atrr9="41297" Atrr10="40616" Atrr11="681">
<Element3>
<SubGroup1>
<Element5>75959</Element5>
<Element6>209</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup1>
<SubGroup4>
<Element5>129687</Element5>
<Element6>283</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup4>
<SubGroup5>
<Element5>223157</Element5>
<Element6>343</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup5>
</Element3>
</Element2>
<Element2 Atrr7="201338" Atrr7="My Reporting Group 2" Atrr9="7372" Atrr10="3552" Atrr11="3820">
<Element3>
<SubGroup2>
<Element5>416931</Element5>
<Element6>498</Element6>
<Element7>4</Element7>
<Element8>0</Element8>
</SubGroup2>
<SubGroup3>
<Element5>66720</Element5>
<Element6>82</Element6>
<Element7>1</Element7>
<Element8>0</Element8>
</SubGroup3>
</Element3>
</Element2>
</Element1>
--------------------
Colt 45 - the original point and click interface -
Ok, do it with string manipulation. You may eliminate inner CAST if building entire string this way. But for redability i just use two CASTs.
if object_id('Tempdb..#Element1') is not null drop table #Element1;
create table #Element1 (
[Attr1] varchar(10)
,[Attr2] varchar(4)
,[Attr3] varchar(2)
,[Attr4] varchar(5)
,[Attr5] integer
,[Attr6] varchar(1)
);
INSERT INTO #Element1
VALUES ('MONTH','2015','07','NEW',39,'Y');
if object_id('Tempdb..#Element2') is not null drop table #Element2;
create table #Element2 (
[Attr5] integer
,[Attr7] varchar(15)
,[Attr8] varchar(100)
,[Attr9] decimal
,[Attr10] decimal
,[Attr11] decimal
);
INSERT INTO #Element2
VALUES (39,'12345','My Reporting Group 1',41297,40616,681)
,(39,'67890','My Reporting Group 2',7372,3552,3820);
if object_id('Tempdb..#Element3') is not null drop table #Element3;
create table #Element3 (
[Attr7] varchar(15)
,[Element4] varchar(50)
,[Element5] decimal
,[Element6] decimal
,[Element7] decimal
,[Element8] decimal
);
INSERT INTO #Element3
VALUES('12345','SubGroup1',75959,209,1,0)
,('12345','SubGroup4',129687,283,1,0)
,('12345','SubGroup5',223157,343,1,0)
,('67890','SubGroup2',416931,498,4,0)
,('67890','SubGroup3',66720,82,1,0);
go
SELECT
e1.[Attr1] [element1/@Attr1]
,e1.[Attr2] as [element1/@Attr2]
,e1.[Attr3] as [element1/@Attr3]
,e1.[Attr4] as [element1/@Attr4]
,e1.[Attr5] as [element1/@Attr5]
,e1.[Attr6] as [element1/@Attr6]
,element1 = (SELECT
e2a.[Attr7] as [@Atrr7]
,e2a.[Attr8] as [@Atrr8]
,e2a.[Attr9] as [@Attr09]
,e2a.[Attr10] as [@Attr10]
,e2a.[Attr11] as [@Attr11]
,element3 = (SELECT CAST('<' + e3.[Element4] +'>' +
CAST(
(SELECT
e3.[Element5]
,e3.[Element6]
,e3.[Element7]
,e3.[Element8]
FOR XML PATH ('')) AS NVARCHAR(4000))
+'</'+ e3.[Element4] +'>' AS XML)
FROM #Element2 e2_2
join #Element3 e3 ON e3.[Attr7] = e2_2.[Attr7]
WHERE e2a.[Attr5] = e2_2.[Attr5]
and e2a.[Attr7] = e2_2.[Attr7]
FOR XML PATH(''), TYPE
)
FROM #Element2 e2a
WHERE e1.[Attr5] = e2a.[Attr5]
FOR XML PATH('element2'), TYPE
)
FROM #Element1 e1
FOR XML PATH(''), ROOT('r')
-
That looks to have done it. Awesome work thanks.
--------------------
Colt 45 - the original point and click interface -
I think it fascinating that the end result in the form of XML is 919 characters (without spaces) and that the code to conditionally drop the tables, recreate the tables, and populate the tables is only 955 characters (without spaces) . The actual data in "Comedy Separated Format" is only 296 characters including the spaces in the data.
And people say that SQL is antiquated! Between the tag bloat and relative unreadability and all the trouble that you have to go to put the data into hierarchical format and all the trouble that someone on the other end will have flattening the data back out into it's respective entities not to mention the quadrupling of space used in all areas concerning backups, diskspace, IO, and traffic over the pipe, let me express just how disgusted I am with XML. Any attempts to express the actual datatypes or guarantee deentitization of reserved characters would only make it take more space and twice as long to pack. :sick:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, surely you're forgetting the times when you need a XSL file and namespaces declarations :-D:-D
--------------------
Colt 45 - the original point and click interface -
philcart (10/8/2015)
Jeff, surely you're forgetting the times when you need a XSL file and namespaces declarations :-D:-DHeh... not forgetting... trying to give it a chance. I figured it was bad enough as it was. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply