June 23, 2011 at 11:59 am
I need to format the output of a query to XML.
There are only two columns in the output: tblCategory.Name and tblCategorySubCategory.Name. The two tables are linked with a FK. Each Category will have 2 to 10 SubCategories. IE:
Automobiles - Cars, Trucks, Motorcycles
Boats - Speedboats, Yachts
The XML format needs to be similar to:
<categories>
<category name="Automobiles">
<subcategory>Cars</subcategory>
<subcategory>Trucks</subcategory>
...
</category>
</categories>
I am hoping to be able to do this without the EXPLICIT option, unless that is the only way.
Thanks in advance!
June 23, 2011 at 12:10 pm
June 23, 2011 at 12:14 pm
You may achieve that using a FOR XML AUTO clause at the end your query. Hope this is what you were looking for.
June 23, 2011 at 1:14 pm
Thanks, Vick. I'm newbie here so happy to see a standard to use.
Provided the test script below, adding the FOR XML AUTO at the end of the select statement returns the following:
<cat Name="Automobiles">
<subCat Name="Cars" />
<subCat Name="Trucks" />
<subCat Name="Motorcycles" />
</cat>
<cat Name="Boats">
<subCat Name="Speed Boat" />
<subCat Name="Yacht" />
</cat>
I want the output to be:
<cat Name="Automobiles">
<subCat>
<Name>Cars</Name>
<Name>Trucks</Name>
<Name>Motorcycles</Name>
</subCat>
<cat Name="Boats">
<subCat>
<Name>Speed Boat</Name>
<Name>Yacht</Name>
</subCat>
</cat>
/**** BEGIN TEST SCRIPT ****/
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#category','U') IS NOT NULL
DROP TABLE #category
IF OBJECT_ID('TempDB..#subCategory','U') IS NOT NULL
DROP TABLE #subCategory
--===== Create the test table with
CREATE TABLE #category
(
CategoryId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name varchar(20)
)
CREATE TABLE #subCategory
(
SubCategoryId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CategoryId INT,
Name varchar(20),
)
--===== Populate Test tables
SET IDENTITY_INSERT #category ON
INSERT INTO #category (CategoryId, Name)
SELECT 1, 'Automobiles' UNION ALL
SELECT 2, 'Boats'
SET IDENTITY_INSERT #category OFF
SET IDENTITY_INSERT #subCategory ON
INSERT INTO #subCategory (SubCategoryId, CategoryId, Name)
SELECT 1, 1, 'Cars' UNION ALL
SELECT 2, 1, 'Trucks' UNION ALL
SELECT 3, 1, 'Motorcycles' UNION ALL
SELECT 4, 2, 'Speed Boat' UNION ALL
SELECT 5, 2, 'Yacht'
SET IDENTITY_INSERT #subCategory OFF
--===== Sample SQL Query
SELECT cat.Name,
subCat.Name
FROM #category cat
JOIN #subCategory subCat
ON cat.CategoryId = subCat.CategoryId
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply