I am trying to generate an output using Productname from below xml which will look like:

B/C/

B/D/E

demo xml:

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
	<Warranty   Productname="E">1 year parts and labor</Warranty>
   </Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/Features/Warranty/@Productname)[1]', 'nvarchar(max)' )
SELECT @ProdID

I have a query which works perfect but  with nodes. similar way I am looking for the value column. I want a similar result from the function on "Value" column(value column is defined inside the below query) : B/D/E ....

with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(c)
  
  union all
  
  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
  union all
  
  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C)
)
select Name, Value
from CTE_xpath
where Value is not null

• Edited by scottichrosaviakosmos 16 hours 58 minutes ago

I am trying to generate an output using Productname from below xml which will look like:

B/C/

B/D/E

demo xml:

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
	<Warranty   Productname="E">1 year parts and labor</Warranty>
   </Warranty>

  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SET @ProdID =  @myDoc.value('(/Root/ProductDescription/Features/Warranty/@Productname)[1]', 'nvarchar(max)' )
SELECT @ProdID

I have a query which works perfect but  with nodes. similar way I am looking for the value column. I want a similar result from the function on "Value" column(value column is defined inside the below query) : B/D/E ....

with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(c)
  
  union all
  
  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
  union all
  
  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C)
)
select Name, Value
from CTE_xpath
where Value is not null

• Merged by Eric__ZhangMicrosoft contingent staff, Moderator 14 minutes ago same thread from one op

Am I in right platform? 

You could also ask this in SQL Server XML forum as well but you can get your answer here as well

its already there. no one replied so i though may be i am on some wrong section.

I googled it and seems there are many ways to play with node name but can't find any on Attribute values .

Right. I looked at this earlier today, but I could not really get a grip of result you want. But there is no simple way to get that list. Here is an ugly version that only gives you the full hierarcy:

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
   <Warranty   Productname="E">1 year parts and labor</Warranty>   </Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'

SELECT @myDoc.value('(/Root/ProductDescription/Features/Warranty/@Productname)[1]', 'nvarchar(max)' )

;with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(C)

  union all

  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
  union all

  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C)
)
select (SELECT Value + '/'
        from CTE_xpath
        where Name LIKE '%/@Productname'
        for xml path(''), TYPE).value('.', 'nvarchar(200)')

Thanks Erlan for help. 

The query which I am using gives me hierarchy path of nodes. I am trying to find a way to get resultset like the other column with node names with respect to their hierarchies. The query you updated is showing flat concatenation of values in 1 row.

We want to show the attribute hierarchy relation on every row of the resultset. one of the obstacles i am facing is that how to find which "warranty" child of which  parent "warranty" node.

is that possible in sql server?.

• Edited by scottichrosaviakosmos 5 hours 53 minutes ago

Hi Scott,

Regarding your description, are you looking for some sample as below?

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
	<Warranty   Productname="E">1 year parts and labor</Warranty>
   </Warranty>
   <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'  
 

;with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(c)
  
  union all
  
  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') +ISNULL('['+T.C.value('./@Productname', 'nvarchar(max)')+']' ,'') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
   union all
  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') + '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C) 
)
select Name, Value
from CTE_xpath
where Value is not null
option(maxrecursion 99)
GO
 

If you have any question, feel free to let me know.

Thank Eric, You are very close. 

 I want to play in a  way with the Attribute values something like you see in the XML Productname=E is a child of Productname=D So the resultset should show for that row should show like below.

Name  Value

Root//ProductDescriptio[A]/Feature[B]/Warranty[D]/Warranty/@Productname     D/E

if one more warranty with Productname=F  is grandchild of D and child of E then name column will have node path till Warrently[F](which you query is already doing) and value column should have value as D/E/F.

Hi scott,

It would be much more complicated to get the value in path. How about the query below.

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features  Productname="B">
  <Warranty   Productname="C">1 year parts and labor</Warranty>
  <Warranty   Productname="D">
	<Warranty   Productname="E">1 year parts and labor</Warranty>
   </Warranty>
   <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'  


 

;with
CTE_xpath as (
  select
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from @myDoc .nodes('*') as T(c)
  
  union all
  
  select
    p.Name + '/' + T.C.value('local-name(.)', 'nvarchar(max)') +ISNULL('['+T.C.value('./@Productname', 'nvarchar(max)')+']' ,'') as Name,
    T.C.query('./*') as elements,
    T.C.value('text()[1]', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*') as T(C)
   union all
  select
    p.Name + '/' +
    T.C.value('local-name(..)', 'nvarchar(max)') +ISNULL('['+T.C.value('../@Productname', 'nvarchar(max)')+']' ,'') 
	+ '/@' +
    T.C.value('local-name(.)', 'nvarchar(max)') as Name,
    null as elements,
    T.C.value('.', 'nvarchar(max)') as Value
  from CTE_xpath as p
    cross apply p.elements.nodes('*/@*') as T(C) 
)
select Name, Value
from CTE_xpath
where Value is not null 
GO

If you have any question, feel free to let me know.

Cheers Eric :)

Yes you are right . It will be very tricky to get path in Value column. Your query is very close to the what I am looking for. This is really helpful. 

I will try  to extract the objects inside [] :)

Thanks Eric Great help.

Hi Scott,

Please check the following SQL Select with CTE and SQL XML query features

The problem here is I assume that maximum number of the nested warranty nodes is known

DECLARE @myDoc xml
DECLARE @ProdID nvarchar(10)
SET @myDoc = '
<Root>
	<ProductDescription Productname="A" Productd="Road Bike">
		<Features  Productname="B">
			<Warranty   Productname="C">1 year parts and labor</Warranty>
			<Warranty   Productname="D">
				<Warranty   Productname="E">1 year parts and labor</Warranty>
			</Warranty>
			<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
		</Features>
	</ProductDescription>
</Root>'

--select @myDoc

;with CTE as (
SELECT
 ProductDescription.value('@Productname','nvarchar(50)') as productname,
 '' as features,
 '' as warranty,
 '' as subwarranty
FROM @myDoc.nodes('/Root/ProductDescription') as XMLtable1(ProductDescription)

union

SELECT
 ProductDescription.value('@Productname','nvarchar(50)') as productname,
 Features.value('@Productname','nvarchar(50)') as features,
 '' as warranty,
 '' as subwarranty
FROM @myDoc.nodes('/Root/ProductDescription') as XMLtable1(ProductDescription)
CROSS APPLY XMLtable1.ProductDescription.nodes('Features') as XMLtable2(Features) 

union

SELECT
 ProductDescription.value('@Productname','nvarchar(50)') as productname,
 Features.value('@Productname','nvarchar(50)') as features,
 Warranty.value('@Productname','nvarchar(50)') as warranty,
 '' as subwarranty
FROM @myDoc.nodes('/Root/ProductDescription') as XMLtable1(ProductDescription)
CROSS APPLY XMLtable1.ProductDescription.nodes('Features') as XMLtable2(Features) 
CROSS APPLY XMLtable2.Features.nodes('Warranty') as XMLtable3(Warranty) 

union

SELECT
 ProductDescription.value('@Productname','nvarchar(50)') as productname,
 Features.value('@Productname','nvarchar(50)') as features,
 Warranty.value('@Productname','nvarchar(50)') as warranty,
 SubWarranty.value('@Productname','nvarchar(50)') as subwarranty
FROM @myDoc.nodes('/Root/ProductDescription') as XMLtable1(ProductDescription)
CROSS APPLY XMLtable1.ProductDescription.nodes('Features') as XMLtable2(Features) 
CROSS APPLY XMLtable2.Features.nodes('Warranty') as XMLtable3(Warranty) 
CROSS APPLY XMLtable3.Warranty.nodes('Warranty') as XMLtable4(SubWarranty) 
)
select
	productname, features, warranty, subwarranty,
	CONCAT(
		IIF(productname = '',NULL,productname + '/'),
		IIF(features = '',NULL,features + '/'),
		IIF(warranty = '',NULL,warranty + '/'),
		IIF(subwarranty = '',NULL,subwarranty + '/')
	)
from (
	select
		*,
		count(*) over (partition by productname) p,
		count(*) over (partition by productname, features) f,
		count(*) over (partition by productname, features, warranty) w,
		count(*) over (partition by productname, features, warranty, subwarranty) s
	from CTE
) t
where
	(p= 1 AND productname <> '') OR
	(p <> 1 AND f = 1 AND features <> '') OR
	(p <> 1 AND f <> 1 AND w = 1 AND warranty <> '') OR
	(p <> 1 AND f <> 1 AND w <> 1 AND s = 1 AND subwarranty <> '')

Here is the output