Using an attribute more than once in an XML T-SQL query with the same element name
I am trying to generate XML result in SQL 2008 using FOR XML Path. This works fine:
<Taxonomy>
<Category Level="1">Clothing</Category>
<SubCategory Level="2">Jeans</SubCategory>
</Taxonomy>
But I would like the result to be:
<Taxonomy>
<Category Level="1">Clothing</Category>
<Category Level="2">Jeans</Category>
</Taxonomy>
You can of course write the following code:
1 as 'Taxonomy/Category/@Level',
2 as 'Taxonomy/Category/@Level',
t.MainCat as 'Taxonomy/Category',
t.SubCat as 'Taxonomy/Category',
But this gives an error message: Attribute-centered column "Column name is repeated. The same attribute cannot be generated more than once on the same XML tag.
What can you do to get the desired result? Will there be a subsection or some kind of cross? Or maybe a union? But how?
---- EDIT - after several answers, the following solution came up:
SELECT
1 as 'Category/@Level',
t.Cat as 'Category'
FROM table t
UNION
SELECT
2 as 'Category/@Level',
t.SubCat as 'Category'
FROM table t
FOR XML PATH (''), ROOT('Taxonomy')
gives this result:
<Taxonomy>
<Category Level="1">Clothing</Category>
<Category Level="2">Jeans</Category>
</Taxonomy>
Still need to figure out how to put this partial encoding into much larger code with multiple "nested" FOR XML already
source to share
The shortcut methods might not cut for this. AUTO and PATH don't like multiple elements with the same name. It looks like you have to use FOR XML EXPLICIT command .
It works, but is cumbersome.
Example:
--Generate Sample Data
--FOR XML EXPLICIT requires two meta fields: Tag and Parent
--Tag is the ID of the current element.
--Parent is the ID of the parent element, or NULL for root element.
DECLARE @DataTable as table
(Tag int NOT NULL
, Parent int
, TaxonomyValue nvarchar(max)
, CategoryValue nvarchar(max)
, CategoryLevel int)
--Fill with sample data: Category Element (2), under Taxonomy(1), with no Taxonomy value.
INSERT INTO @DataTable
VALUES (2, 1, NULL, 1, 'Clothing')
, (2, 1, NULL, 2, 'Jeans')
--First part of query: Define the XML structure
SELECT
1 as Tag --root element
, NULL as Parent
, NULL as [Taxonomy!1] --Assign Taxonomy Element to the first element, aka root.
, NULL as [Category!2] --Assign Category Element as a child to Taxonomy.
, NULL as [Category!2!Level] --Give Category an Attribute 'Level'
--The actual data to fill the XML
UNION
SELECT
Data.Tag
, Data.Parent
, Data.TaxonomyValue
, Data.CategoryValue
, Data.CategoryLevel
FROM
@DataTable as Data
FOR XML EXPLICIT
Generates XML
<Taxonomy>
<Category Level="1">Clothing</Category>
<Category Level="2">Jeans</Category>
</Taxonomy>
Edit: If the columns were canceled. No more Jeans level.
source to share
I know this is an old post, but I want to share one solution that avoids the complexity of FOR XML EXPLICIT command for large xmls.
It is enough to add null as a child of the Taxonomy and the error disappears:
select 1 as 'Taxonomy/Category/@Level',
t.MainCat as 'Taxonomy/Category',
NULL AS 'Taxonomy/*',
2 as 'Taxonomy/Category/@Level',
t.SubCat as 'Taxonomy/Category',
from t
Hope this helps.
source to share
I have another way. It was a little easier for me. Let's say for example I have xml like
DECLARE @xml xml='<parameters></parameters>'
DECLARE @multiplenodes XML = '<test1><test2 Level="1">This is a test node 2</test2><test2 Level="2">This is another test node</test2></test1>'
SET @xml.modify('insert sql:variable("@multiplenodes") into (/parameters)[1]')
SELECT @xml
Tell me if this helps.
source to share