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