Using attribute more than once in FOR XML Path T-SQL query with same element name

Question

I am trying to create an xml output in SQL 2008 using FOR XML Path. This is working fine:

<Taxonomy>
      <Category Level="1">Clothing</Category>
      <SubCategory Level="2">Jeans</SubCategory>
 </Taxonomy>

But I would like the output to be:

<Taxonomy>
      <Category Level="1">Clothing</Category>
      <Category Level="2">Jeans</Category>
</Taxonomy>

Of course you can code as following:

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-centric column 'Column name is repeated. The same attribute cannot be generated more than once on the same XML tag.

What can be done to get the desired output? Would a subselect work or some kind of cross apply? Or perhaps a union? But how?

---- EDIT - after several answers came up with following solution:

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 output:

<Taxonomy>
  <Category Level="1">Clothing</Category>
  <Category Level="2">Jeans</Category>
</Taxonomy>

Still have to figure out how to put this partial coding in a much larger code with several 'nested' FOR XML's already

Is your real query intended to produce multiple Taxonomy elements? I.e. one element per `t` (whatever `t` is) ? — AaronLS, Jul 24 '14 at 22:11
Yes, one element per Category, each with an attribute for the level (hierarchy) of the category — J3FFK, Jul 24 '14 at 22:23

score 2 · Accepted Answer · answered Jul 24 '14 at 22:23

The shortcut methods may not cut it for this. AUTO and PATH don't like multiple elements with the same name. Looks like you would have to use the FOR XML EXPLICIT command.

It works, but is cumbersome.

Sample:

--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: Had columns reversed. No more Jeans level.

I thought about using XML Explicit but I usually try to avoid it because it is quite difficult and most of the time XML Path does the job. But this certainly does work, thanks! — J3FFK, Jul 24 '14 at 22:29

score 2 · Answer 2 · answered Dec 03 '15 at 14:28

I know this is an old post, but I want to share one solution that avoids that FOR XML EXPLICIT command complexity for big xmls.

It's enough to add null as a child of Taxonomy, and error will disappear:

    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

I hope it helps.

score 0 · Answer 3 · answered Jul 25 '14 at 08:44

0

I have another way. It seemed a tad bit easy to me. Say, for example I have an 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

Do tell me if this helps.

answered Jul 25 '14 at 08:44

vstandsforvinay

138
1
11

Thanks, but this does not change TEST2 element into TEST element. In other words, your example already has the correct same columnname and thus elementname. In my example Category1 and Category2 need to be changed to Category. Do you have any idea if that is possible using xml.modify? – J3FFK Jul 30 '14 at 15:34
i didn't get what you are trying to say. Do you need the levels to be automatically created or what? – vstandsforvinay Jul 31 '14 at 11:17
the elements Category1 and Category2 need to become both Category element. They are generated with a sql select, converted to xml via xml path. Evere column must be unique and there every element will be unique, but I need the Category element name to be the same. Hope I explained it this way. Thanks! – J3FFK Jul 31 '14 at 13:36

Using attribute more than once in FOR XML Path T-SQL query with same element name

3 Answers3

Linked