Get part of xml

Question

I'm trying to get part of xml like one column in select.

<Items>

<Item Name = "Item1">

<Attributes>

<Attribute Name=Attribute1 />

<Attribute Name = Attribute2 />

</Attributes>

</Item>

<Item Name = "Item2">

<Attributes>

<Attribute Name=Attribute1 />

<Attribute Name = Attribute2 />

</Attributes>

</Item>

</Items>

I need to get this result:

Item  Attributes

---------------------------------------

Item1 <Attributes><Attribute Name=Attribute1/> <Attribute Name = Attribute2 /></Attributes>

Item2 <Attributes><Attribute Name=Attribute1/> <Attribute Name = Attribute2 /></Attributes>

I've tried to use this select : SELECT n.value('@Name', 'NVARCHAR(255)') AS ItemName, n.query('/Attributes') AS Attributes  

FROM @message.nodes('/Items/Item') m ( n )

If anyone could point me in the right direction I would be very grateful.

Thanks 

Ondrej

Answer 1

First of all: Post correct XML data..

Your solution has only one minor bug. The effective XPath is build from the value in the nodes query and the query method. Thus you have a / too much. Here's the corrected version:

DECLARE @Data XML = N'
<Items>
	<Item Name="Item1">
		<Attributes>
			<Attribute Name="Attribute1"/>
			<Attribute Name="Attribute2"/>
		</Attributes>
	</Item>
	<Item Name="Item2">
		<Attributes>
			<Attribute Name="Attribute1"/>
			<Attribute Name="Attribute2"/>
		</Attributes>
	</Item>
</Items>
';

SELECT  n.value('@Name', 'NVARCHAR(255)') AS ItemName ,
        n.query('Attributes') AS Attributes
FROM    @Data.nodes('/Items/Item') D ( n );

Answer 2

Thanks for your help.

Answer 3

Are you trying to use your XML page to run in SQL Server, I had the similar issue, then what I did was used entity framework. First take your xml data to the framework then send it to SQL with the SQL Command(defined as String), then you should query as you wanted to your origional xml data.

---

Please mark the replies as answers if they help, Thanks.

---

Pranam Manandhar