FOR XML PATH question

Hello all,

I am trying to get a list of materials needed for and item in an xml
format such as

<item ...attributes...>
<material ...attributes.../>
<material ...attributes.../>
</item>

I have the following query built, but it gives me (would there have
been 2 materials needed)
<item ..attributes...>
<material ...attributes.../>
</item>
<item (same item)..attributes...>
<material ...attributes.../>
</item>


SELECT item.typeID AS "@typeID",
ig.groupName AS "@groupName",
item.typeName AS "@typeName",
material.requiredTypeID AS "Material/@typeID",
(SELECT item.typeName
FROM invTypes item
WHERE item.typeID = material.requiredTypeID) AS "Material/
@typeName",
material.quantity AS "Material/@quantity"
FROM invTypes item INNER JOIN typeActivityMaterials material ON
item.typeID = material.typeID
INNER JOIN invGroups ig ON item.groupID = ig.groupID
WHERE item.published = 1
ORDER BY item.typeID
FOR XML PATH('Item'), ROOT('Items')

Something like this?

SELECT item.typeID AS "@typeID",
ig.groupName AS "@groupName",
item.typeName AS "@typeName",
(
SELECT
material.requiredTypeID AS "Material/@typeID",
item2.typeName AS "Material/@typeName",
material.quantity AS "Material/@quantity"
FROM typeActivityMaterials material
INNER JOIN invTypes item2 ON item2.typeID = material.requiredTypeID
WHERE item.typeID = material.typeID FOR XML PATH(''), TYPE
)
FROM invTypes item
INNER JOIN invGroups ig ON item.groupID = ig.groupID
WHERE item.published = 1
ORDER BY item.typeID
FOR XML PATH('Item'), ROOT('Items')

It's hard to tell without sample data tho.

Let me know how you get on.

wBob


"Sean" wrote:

> Hello all,
>
> I am trying to get a list of materials needed for and item in an xml
> format such as
>
> <item ...attributes...>
> <material ...attributes.../>
> <material ...attributes.../>
> </item>
>
> I have the following query built, but it gives me (would there have
> been 2 materials needed)
> <item ..attributes...>
> <material ...attributes.../>
> </item>
> <item (same item)..attributes...>
> <material ...attributes.../>
> </item>
>
>
> SELECT item.typeID AS "@typeID",
> ig.groupName AS "@groupName",
> item.typeName AS "@typeName",
> material.requiredTypeID AS "Material/@typeID",
> (SELECT item.typeName
> FROM invTypes item
> WHERE item.typeID = material.requiredTypeID) AS "Material/
> @typeName",
> material.quantity AS "Material/@quantity"
> FROM invTypes item INNER JOIN typeActivityMaterials material ON
> item.typeID = material.typeID
> INNER JOIN invGroups ig ON item.groupID = ig.groupID
> WHERE item.published = 1
> ORDER BY item.typeID
> FOR XML PATH('Item'), ROOT('Items')
>