SQL Server: Two-level GROUP BY with XML output
declare @T table
(
ID int,
Type varchar(30),
SubType varchar(30),
SubSubType varchar(30)
)
insert into @T values
(1, 'Product Documentation', 'Brochures', 'Functional Brochures'),
(2, 'Product Documentation', 'Brochures', 'Fliers'),
(3, 'Product Documentation', 'Data Sheets and Catalogs', 'Data Sheets'),
(4, 'Product Documentation', 'Data Sheets and Catalogs', 'Catalogs'),
(5, 'Other Documentation', 'Other classification', 'User Guides')
select T1.Type as '@Name',
(
select T2.SubType as '@Name',
(
select T3.SubSubType as '@Name'
from @T as T3
where T3.SubType = T2.SubType and
T3.Type = T1.Type
for xml path('SubSubType'), type
)
from @T as T2
where T2.Type = T1.Type
group by T2.SubType
for xml path('SubType'), type
)
from @T as T1
group by Type
for xml path('Type'), root('AllTypes')
SQL XML grouping
You need to GROUP BY . Simplified example
WITH [CPCounting] AS(
select 1 as [CarparkNo], 100 [CountingCategoryNo] , 'aa' [CountingCategory]
union all
select 1 as [CarparkNo], 200 [CountingCategoryNo] , 'bb' [CountingCategory]
)
SELECT [CarparkNo]
,(SELECT [CountingCategoryNo]
,[CountingCategory]
FROM [CPCounting] c2
WHERE c2.[CarparkNo] = c1.[CarparkNo]
FOR XML PATH('CountingCategory'), TYPE
)
FROM [CPCounting] c1
GROUP BY [CarparkNo]
FOR XML PATH('Carpark'), ROOT('Carparks'), TYPE;
Returns
<Carparks>
<Carpark>
<CarparkNo>1</CarparkNo>
<CountingCategory>
<CountingCategoryNo>100</CountingCategoryNo>
<CountingCategory>aa</CountingCategory>
</CountingCategory>
<CountingCategory>
<CountingCategoryNo>200</CountingCategoryNo>
<CountingCategory>bb</CountingCategory>
</CountingCategory>
</Carpark>
</Carparks>
Converting SQL query result to XML
Try This
FIDDLE DEMO
SELECT ID, Year, Name,
(
SELECT ProvID AS 'Prov/@ProvID',Prov
FROM tbl t
WHERE ID = t.ID AND Name = t.Name
FOR XML PATH(''),TYPE
)
FROM tbl
GROUP BY ID, Year, Name
FOR XML PATH ('Entry'),ROOT('legref')
Output
<legref>
<Entry>
<ID>1</ID>
<Year>1995</Year>
<Name>MAC</Name>
<Prov ProvID="1995-11_CL236">Reg 236</Prov>
<Prov ProvID="1995-11_CL230">Reg 230</Prov>
<Prov ProvID="1995-11_CL229J">Reg 229J</Prov>
<Prov ProvID="1995-11_CL260">Reg 260</Prov>
</Entry>
</legref>
Generate dynamic multilevel XML file from SQL Server table
I have taken your "An example of how I need to do" and replaced level 4 and level 5 queries with this:
-- Levels 3+
SELECT prod.ProductName AS '@id', prod.GroupLevel AS '@PreviousGroupLevel', prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel>= 3
--FOR XML PATH('productLevel_previously_level_4'),ROOT('products'),TYPE
FOR XML PATH('productLevel_HigherLevels'),ROOT('products'),TYPE
In the above, notice, that I am using an attribute "@PreviousGroupLevel" and that XML PATH changed to "productLevel_HigherLevels".
The overall query is below (it requires #tmpFullResult table):
SELECT lvl1.GroupName AS groupName,
(
SELECT prod1.ProductName, prod1.ProductNumber AS itemNumber
,prod1.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod1
WHERE prod1.ProductID IS NOT NULL
AND prod1.GroupLevel = 1
AND prod1.ProductID = lvl1.ProductID
AND prod1.GroupID = lvl1.GroupID
FOR XML PATH('productLevel_1'),ROOT('products'),TYPE
),
(
SELECT lvl2.GroupName AS 'GroupName',
(
SELECT prod2.ProductName AS ProductName ,prod2.ProductNumber AS itemNumber,
prod2.ProductPrice AS itemPrice, prod2.GroupID
FROM #tmpFullResult AS prod2
WHERE prod2.ProductID IS NOT NULL
AND prod2.GroupLevel=2
AND prod2.ProductID=lvl2.ProductID
AND prod2.GroupID=lvl2.GroupID
ORDER BY groupName
FOR XML PATH('productLevel_2'),ROOT('products'),TYPE
),
(
SELECT lvl3.GroupName AS groupName
,(
SELECT prod.ProductName AS '@id' ,prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel = 3
AND prod.ProductID = lvl3.ProductID
AND prod.GroupID = lvl3.GroupID
FOR XML PATH('productLevel_3'),ROOT('products'),TYPE
)
,(
-- Levels 3+
SELECT prod.ProductName AS '@id', prod.GroupLevel AS '@PreviousGroupLevel', prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel>= 3
--FOR XML PATH('productLevel_previously_level_4'),ROOT('products'),TYPE
FOR XML PATH('productLevel_HigherLevels'),ROOT('products'),TYPE
)/*,
(
SELECT prod.ProductName AS '@id' ,prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel=5
FOR XML PATH('productLevel_previously_level_5'),ROOT('products'),TYPE
)*/
FROM #tmpFullResult AS lvl3
WHERE lvl3.GroupLevel=3
AND lvl3.ParentGroupID=lvl2.GroupID
FOR XML PATH('productGroup3'),TYPE, ELEMENTS
)
FROM #tmpFullResult AS lvl2
WHERE lvl2.GroupLevel=2
AND lvl2.ParentGroupID=lvl1.GroupID
order by lvl2.GroupName
FOR XML PATH('productGroup2'),TYPE, ELEMENTS
)
FROM #tmpFullResult AS lvl1
WHERE lvl1.ParentGroupID IS NULL
order by GroupNumber
FOR XML PATH('productGroup1'),ROOT('root')
I hope this is what you have been looking for.
Update:
Sample data:
CREATE TABLE #tmpFullResult( GroupLevel INT, GroupID VARCHAR( 20 ) NULL,
GroupLanguageID VARCHAR( 20 ) NULL, GroupName VARCHAR( 20 ) NULL,
GroupNumber DECIMAL( 10, 3 ) NULL, ParentGroupID VARCHAR( 20 ) NULL,
GroupProductRelationGroupID VARCHAR( 20 ) NULL,
GroupProductRelationProductID VARCHAR( 20 ) NULL,
ProductID VARCHAR( 20 ) NULL, ProductLanguageID VARCHAR( 10 ) NULL,
ProductNumber INT NULL, ProductName VARCHAR( 20 ) NULL, ProductPrice DECIMAL( 20, 4 ) NULL )
INSERT INTO #tmpFullResult
VALUES
( 1, '1@@SHOP1', 'LANG2', 'ABC', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
( 2, '1.01@@SHOP1', 'LANG2', 'BCD', 1.01, '1@@SHOP1', '1.01@@SHOP1', '457163@@SHOP1', '457163@@SHOP1', 'LANG2', 457163, 'ProductName', 299.81 ),
( 2, '1.01@@SHOP1', 'LANG2', 'BCD', 1.01, '1@@SHOP1', '1.01@@SHOP1', '457162@@SHOP1', '457162@@SHOP1', 'LANG2', 457162, 'ProductName', 163.14 ),
( 2, '1.03@@SHOP1', 'LANG2', 'DEF', 1.03, '1@@SHOP1', '1.03@@SHOP1', '159151@@SHOP1', '159151@@SHOP1', 'LANG2', 159151, 'ProductName', 10 ),
( 2, '1.03@@SHOP1', 'LANG2', 'DEF', 1.03, '1@@SHOP1', '1.03@@SHOP1', '159150@@SHOP1', '159150@@SHOP1', 'LANG2', 159150, 'ProductName', 10 ),
( 2, '1.03@@SHOP1', 'LANG2', 'DEF', 1.02, '1@@SHOP1', '1.03@@SHOP1', '159122@@SHOP1', '159122@@SHOP1', 'LANG2', 159122, 'ProductName', 309.35 ),
( 3, '1.13@@SHOP1', 'LANG2', 'GGG', 1.13, '1.03@@SHOP1', '1.03@@SHOP1', '3459652@@SHOP1','3459652@@SHOP1','LANG2', 3459652, 'ProductName', 309.35 ),
( 4, '1.14@@SHOP1', 'LANG2', 'BBB', 1.14, '1.13@@SHOP1', '1.13@@SHOP1', '564326@@SHOP1', '564326@@SHOP1', 'LANG2', 564326, 'ProductName', 309.35 ),
( 5, '1.15@@SHOP1', 'LANG2', 'BBB', 1.14, '1.14@@SHOP1', '1.13@@SHOP1', '564326@@SHOP1', '564326@@SHOP1', 'LANG2', 564326, 'ProductName', 309.35 ),
( 3, '1.23@@SHOP1', 'LANG2', 'GGG', 1.13, '1.01@@SHOP1', '1.03@@SHOP1', '3459652@@SHOP1','3459652@@SHOP1','LANG2', 3459652, 'ProductName', 309.35 ),
( 4, '1.24@@SHOP1', 'LANG2', 'BBB', 1.14, '1.23@@SHOP1', '1.13@@SHOP1', '564326@@SHOP1', '564326@@SHOP1', 'LANG2', 564326, 'ProductName', 309.35 ),
( 5, '1.25@@SHOP1', 'LANG2', 'BBB', 1.14, '1.24@@SHOP1', '1.13@@SHOP1', '564326@@SHOP1', '564326@@SHOP1', 'LANG2', 564326, 'ProductName', 309.35 )
Below query is similar to the one above, except that I have added a CTE to calculate GroupLevel 3 parentID for all higher level groups:
;WITH productLevel_HigherLevels( GroupLevel, GroupID, GroupLanguageID, GroupName, GroupNumber, ParentGroupID,
GroupProductRelationGroupID, GroupProductRelationProductID, ProductID, ProductLanguageID,
ProductNumber, ProductName, ProductPrice )
AS
-- Define the CTE query.
(
SELECT GroupLevel, GroupID, GroupLanguageID, GroupName, GroupNumber, ParentGroupID,
GroupProductRelationGroupID, GroupProductRelationProductID, ProductID, ProductLanguageID,
ProductNumber, ProductName, ProductPrice
FROM #tmpFullResult
WHERE GroupLevel = 4 AND ProductID IS NOT NULL
UNION ALL
SELECT HigherLevel.GroupLevel, HigherLevel.GroupID, HigherLevel.GroupLanguageID, HigherLevel.GroupName, HigherLevel.GroupNumber, LowerLevel.ParentGroupID,
HigherLevel.GroupProductRelationGroupID, HigherLevel.GroupProductRelationProductID, HigherLevel.ProductID, HigherLevel.ProductLanguageID,
HigherLevel.ProductNumber, HigherLevel.ProductName, HigherLevel.ProductPrice
FROM productLevel_HigherLevels AS LowerLevel
INNER JOIN #tmpFullResult AS HigherLevel
ON HigherLevel.ProductID IS NOT NULL
AND LowerLevel.GroupLevel + 1 = HigherLevel.GroupLevel
AND HigherLevel.ParentGroupID = LowerLevel.GroupID
)
SELECT lvl1.GroupName AS groupName,
(
SELECT prod1.ProductName, prod1.ProductNumber AS itemNumber
,prod1.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod1
WHERE prod1.ProductID IS NOT NULL
AND prod1.GroupLevel = 1
AND prod1.ProductID = lvl1.ProductID
AND prod1.GroupID = lvl1.GroupID
FOR XML PATH('productLevel_1'),ROOT('products'),TYPE
),
(
SELECT lvl2.GroupName AS 'GroupName',
(
SELECT prod2.ProductName AS ProductName ,prod2.ProductNumber AS itemNumber,
prod2.ProductPrice AS itemPrice, prod2.GroupID
FROM #tmpFullResult AS prod2
WHERE prod2.ProductID IS NOT NULL
AND prod2.GroupLevel=2
AND prod2.ProductID=lvl2.ProductID
AND prod2.GroupID=lvl2.GroupID
ORDER BY groupName
FOR XML PATH('productLevel_2'),ROOT('products'),TYPE
),
(
SELECT lvl3.GroupName AS groupName
,(
SELECT prod.ProductName AS '@id' ,prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel = 3
AND prod.ProductID = lvl3.ProductID
AND prod.GroupID = lvl3.GroupID
FOR XML PATH('productLevel_3'),ROOT('products'),TYPE
)
,(
SELECT prod.ProductName AS '@id', prod.GroupLevel AS '@PreviousGroupLevel', prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM productLevel_HigherLevels AS prod
WHERE prod.ParentGroupID = lvl3.GroupID
--FOR XML PATH('productLevel_previously_level_4'),ROOT('products'),TYPE
FOR XML PATH('productLevel_HigherLevels'),ROOT('products'),TYPE
)/*,
(
SELECT prod.ProductName AS '@id' ,prod.ProductNumber AS itemNumber,
prod.ProductPrice AS itemPrice
FROM #tmpFullResult AS prod
WHERE prod.ProductID IS NOT NULL
AND prod.GroupLevel=5
FOR XML PATH('productLevel_previously_level_5'),ROOT('products'),TYPE
)*/
FROM #tmpFullResult AS lvl3
WHERE lvl3.GroupLevel=3
AND lvl3.ParentGroupID=lvl2.GroupID
FOR XML PATH('productGroup3'),TYPE, ELEMENTS
)
FROM #tmpFullResult AS lvl2
WHERE lvl2.GroupLevel=2
AND lvl2.ParentGroupID=lvl1.GroupID
order by lvl2.GroupName
FOR XML PATH('productGroup2'),TYPE, ELEMENTS
)
FROM #tmpFullResult AS lvl1
WHERE lvl1.ParentGroupID IS NULL
order by GroupNumber
FOR XML PATH('productGroup1'),ROOT('root')
SQL Table result in a xml output
Your question is quite fuzzy, but my magic crystall ball tells me, that you are looking for this:
DECLARE @tbl TABLE([Key] INT, code INT);
INSERT INTO @tbl VALUES
(1,100)
,(1,200)
,(1,300)
,(1,400)
,(2,100)
,(2,200)
,(2,300);
--The query will first find a distinct list of keys and then use nested FOR XML-selects to gather your data into the structure wanted:
WITH DistinctKeys AS
(SELECT [Key] FROM @tbl GROUP BY [Key])
SELECT dk.[Key]
,(
SELECT dk.[Key]
,(
SELECT t.code
FROM @tbl AS t
WHERE t.[Key]=dk.[Key]
FOR XML PATH(''),ROOT('list'),TYPE
)
FOR XML PATH('sub'),TYPE
) AS XML_Data
FROm DistinctKeys AS dk
The result
Key XML_Data
1 <sub><Key>1</Key><list><code>100</code><code>200</code><code>300</code><code>400</code></list></sub>
2 <sub><Key>2</Key><list><code>100</code><code>200</code><code>300</code></list></sub>
SQL For XML Path - nested results
It can be done.
select
a1.YearPart as '@Year',
( select MonthPart as '@Month',
(select DayPart as '@Day',
(select
lib_title as '@RecordName',
PID as '@RecordID'
from @agenda as a4
where a4.DayPart = a3.DayPart and
a4.MonthPart = a2.MonthPart and
a4.YearPart = a1.YearPart
for xml path('Item'), type
)
from @agenda as a3
where a3.YearPart = a1.YearPart and
a3.MonthPart = a2.MonthPart
group by a3.DayPart
for xml path('Day'), type
)
from @agenda as a2
where a1.YearPart = a2.YearPart
group by a2.MonthPart
for xml path('Month'), type
)
from @agenda as a1
group by YearPart
for xml path('Year'), root
Group XML Column Data
Of course there is a simpler way:
declare @t table (
GroupId [int] NOT NULL,
RawXml [xml] NULL
);
INSERT INTO @t
VALUES (1, '<Item><Criteria Type="State" Values="CA"/><Criteria Type="State" Values="TX"/><Criteria Type="State" Values="FL"/></Item>'),
(1, '<Item><Criteria Type="Manager" Values="Tim"/></Item>'),
(2, '<Item><Criteria Type="Name" Values="Walters"/></Item>'),
(2, '<Item><Criteria Type="Manager" Values="Tim"/></Item>');
select sq.GroupId as [@GroupId],
(
select t.RawXml as [node()]
from @t t
where t.GroupId = sq.GroupId
for xml path(''), type
)
from (select distinct g.GroupId from @t g) sq
for xml path('Group'), type, root('Parent');
(I have replaced your original table with the equivalent TV because, well, I don't like to clean up databases of such tables afterwards. It doesn't introduce any difference with regards to the question.)
Related Topics
Subtract Hours from the Now() Function
How to Bulk Update Sequence Id Postgresql for All Tables
Using Case Statement Inside in Clause
SQL to Determine Minimum Sequential Days of Access
How to Copy a Row from One SQL Server Table to Another
Import Excel Data into Postgresql 9.3
Generating SQL Insert into for Oracle
Alter a SQL Server Function to Accept New Optional Parameter
Is Inner Join the Same as Equi-Join
How to Return Default Value from SQL Query
Unexpected Results from SQL Query with Between Timestamps
SQL Server, Can't Insert Null into Primary Key Field
How to Check for Is Not Null and Is Not Empty String in SQL Server
Copy a Table (Including Indexes) in Postgres