June 29, 2015 at 8:38 pm
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">1 year parts and labor</Warranty>
<Warranty Productname="E">1 year parts and labor</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
June 29, 2015 at 11:01 pm
There are few ways of doing this, but can you tell us more about the data, how big are the sets, more details about the structure etc. so we can find the best solution.
😎
One is to retrieve the element-attribute-value as an EAV type set, here is a quick demo.
USE tempdb;
GO
SET NOCOUNT ON;
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">1 year parts and labor</Warranty>
<Warranty Productname="E">1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
/* EAV type parsing */
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS EAV_RID
,DOC.DATA.value('local-name(../.)','varchar(50)') AS ELEMENT_NAME
,DOC.DATA.value('local-name(.)','varchar(50)') AS ATTRIBUTE_NAME
,DOC.DATA.value('.','varchar(50)') AS ATTRIBUTE_VALUE
from @myDoc.nodes('//*/@*') AS DOC(DATA);
Results
EAV_RID ELEMENT_NAME ATTRIBUTE_NAME ATTRIBUTE_VALUE
-------- -------------------- --------------- ----------------
1 ProductDescription Productname A
2 ProductDescription Productd Road Bike
3 Features Productname B
4 Warranty Productname C
5 Warranty Productname D
6 Warranty Productname E
June 29, 2015 at 11:32 pm
Thanks Eirikur.
I am looking for an output with resultset for value column as :
B/C/
B/D/E
I found one way with below query but this is giving desire output on fullpath column. I want something similar for value column.
Any idea how to do that?.
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
June 29, 2015 at 11:33 pm
June 30, 2015 at 12:13 am
Correct XML:
DECLARE @myDoc xml
SET @myDoc = '<Root>
<ProductDescription Productname="A" Productd="Road Bike">
<Features Productname="B">
<Warranty Productname="C">1 year parts and labor</Warranty>
<Warranty Productname="D">1 year parts and labor
<Warranty Productname="E">1 year parts and labor</Warranty>
</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>'
June 30, 2015 at 8:06 am
can someone help me on this.
September 1, 2015 at 4:41 am
select replace( cast(
@myDoc.query('for $e
in ( /Root/ProductDescription/Features/Warranty/@Productname )
return string( $e )
' ) as varchar(100)) , space(1) , '/' )
Result is C/D/E
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply