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


I am trying to create an XML output for the XML path in SQL 2008. It's working fine:

  & lt; Classification & gt; & Lt; Category level = "1" & gt; Clothing & lt; / Category & gt; & Lt; Sub-class level = "2" & gt; Jeans & lt; / Sub-class & gt; & Lt; / Classification & gt;   

But I should be output:

  & lt; Classification & gt; & Lt; Category level = "1" & gt; Clothing & lt; / Category & gt; & Lt; Category level = "2" & gt; Jeans & lt; / Category & gt; & Lt; / Classification & gt; Of course you can code as:  
  1 as' classification / category / @ level ', 2 as' Classification / Category / Level @, T. 'Categorization / Category' as Man Cat, T. Shebet as a 'classification / category',   

but it returns an error message: attribute-centered column 'column is repeated. The same properties can not be created more than once on the same XML tag.

What can be done to get the desired output? Will a sub-selection task or any kind of cross apply? Or perhaps a union? But how?

---- Edit - After answering several answers with the following solutions:

  'Category / @ level' as SELECT 1, T.Kat Select the unit from 'Category' category 2 as 'Category / @ level', t. Shebat is derived from the table for the XML paragraph (''), root ('classification') as a 'category',   

returns this output:

  & lt; Classification & gt; & Lt; Category level = "1" & gt; Clothing & lt; / Category & gt; & Lt; Category level = "2" & gt; Jeans & lt; / Category & gt; & Lt; / Classification & gt;   

Still, to find out how much code has been made for partial coding with many 'nested' for XML first

< Div class = "post-text" itemprop = "text">

Shortcut methods can not be deduced for this. Auto and path do not like many elements with the same name. It seems that you have to use the command.

It works but is cumbersome.

Sample:

  - Generate sample data - XML ​​expression requires two meta fields: Tag and parent - tag is the current element's ID. - The original root element is zero for the id or the root element. Fill the table data with: Table Element (2), under the classification (1), with no table (tag) with tag (tag not compulsory zero, parent int, categorization value nvarchar (max), category value nvarchar (Maximum) classification value in dataset value (2, 1, zero, 1, 'clothing'), (2, 1, zero, 2, 'jeans') - the first part of the query: XML structure as the tag structure Select 1 Define element, NULL as mother, as NULL [ Classification! 1] - first element classified element classification, aka root., As the tap [class! 2] - classification element element as a child for classification., As the NULL [class! 2! Level] - A specialty for the category 'Level' - The actual data to fill the selection of XML union data, data. Parent, data.Technology value, data .category value, data. Category Level FROM @ Day for Datasheet XML Explorer   

generates XML as a ta

  classification> class = "1" & gt; textile & Lt; / category & gt; & Lt; Category level = "2" & gt; Jeans & lt; / Category & gt; & Lt; / Classification & gt;   

Editing: The column was reversed. Some other jeans level

Comments

Popular posts from this blog

ios - Adding an SKSpriteNode to SKScene from a child SKSpriteNode -

Matlab transpose a table vector -

c# - Textbox not clickable but editable -