Display duplicate xml element values in rows in tsql




declare @doc xml; SET @doc =
'<Cars>
<Car>
<Details>
<Name>corolla</Name>
<Company>Toyota</Company>
</Details>
<Parts>
<Part>steering</Part>
<Part>Exhauster</Part>
<Part>doors</Part>
<Part>Breaks</Part>
</Parts>
</Car>
<Car>
<Details>
<Name>Pilot</Name>
<Company>Honda</Company>
</Details>
<Parts>
<Part>steering</Part>
<Part>Exhauster</Part>
<Part>doors</Part>
</Parts>
</Car>
</Cars>';




With the below query, getting below results


SELECT data.col.query('Details/Name/./text()') as Name, data.col.query('Details/Company/./text ()') as Company, data.col.query('Parts/Part/./text()') as Parts FROM @doc.nodes('(Cars/Car)') AS data(col)



Name Company Parts
----------------------------------------------
corolla Toyota steeringExhausterdoorsBreaks
Pilot Honda steeringExhausterdoors





Expecting the following result

Name Company Parts
-----------------------------------
Corolla Toyota steering
Corolla Toyota Exhauster
Corolla Toyota doors
Corolla Toyota Breaks
Pilot Honda steering
Pilot Honda Exhauster
Pilot Honda doors





Tried few examples but didn't succeed. Please suggest where I'm missing. Thanks in advance

No comments:

Post a Comment