need to add childs o parents

[carloshgogmail]

this is what i have

Complete Database is attached on access format

Sample table
Category Model Model Parent Item Item Explosion Num Explosion Level
PDP5807SYSHD TH-58PH10UKA TH-58PH10UKA MPPDP-20071001 1 1
PDP5807SYSHD TH-58PH10UKA TH-58PH10UKA TK1NWPTU 2 1
PDP5807SYSHD TH-58PH10UKA TK1NWPTU T4F2AX0041 3 2
PDP5807SYSHD TH-58PH10UKA TK1NWPTU T5E7756 4 2
PDP5807SYSHD TH-58PH10UKA TK1NWPTU T5EJ09750 5 2
PDP5807SYSHD TH-58PH10UKA TK1NWPTU TK2NWPTU 6 2
PDP5807SYSHD TH-58PH10UKA TK2NWPTU THEL0429 7 3
PDP5807SYSHD TH-58PH10UKA TK2NWPTU THTF011N 8 3
PDP5807SYSHD TH-58PH10UKA TK2NWPTU TK3NWPTU 9 3
PDP5807SYSHD TH-58PH10UKA TK3NWPTU K5D103BMA001 10 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU K5D502BNA007 11 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU THTF011N 12 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM2A19005-1 13 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM2AA19005-1 14 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM2AD00701 15 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM2AD00801 16 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM2AD00901 17 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TMM6463-1 18 4
PDP5807SYSHD TH-58PH10UKA TK3NWPTU TQFD532 19 4
PDP5807SYSHD TH-58PH10UKA TQFD532 T4EX216005 20 5
PDP5807SYSHD TH-58PH10UKA TQFD532 TYZQF014 21 5


I need to get this result

<ModelCategory model_x0020_category="PDP5807SYSHD">
<Models Model="TH-58PH10UKA">
<Items Item="MPPDP-20071001" />
<Items Item="TK1NWPTU" />
<Items Item="T4F2AX0041" />
<Items Item="T5E7756" />
<Items Item="T5EJ09750" />
<Items Item="TK2NWPTU" />
<Items Item="THEL0429" />
<Items Item="THTF011N" />
<Items Item="TK3NWPTU" />
<Items Item="K5D103BMA001" />
<Items Item="K5D502BNA007" />
<Items Item="THTF011N " />
<Items Item="TMM2A19005-1" />
<Items Item="TMM2AA19005-1" />
<Items Item="TMM2AD00701" />
<Items Item="TMM2AD00801" />
<Items Item="TMM2AD00901" />
<Items Item="TMM6463-1" />
<Items Item="T4EX216005" />
<Items Item="TYZQF014" />
</Models>
</ModelCategory>




This is what I have already and no idea on how to get the desire result

<ModelCategory model_x0020_category="PDP5807SYSHD">
<Models Model="TH-58PH10UKA">
<Items Item="MPPDP-20071001" />
<Items Item="TK1NWPTU" />
<Items Item="T4F2AX0041" />
<Items Item="T5E7756" />
<Items Item="T5EJ09750" />
<Items Item="TK2NWPTU" />
<Items Item="THEL0429" />
<Items Item="THTF011N" />
<Items Item="TK3NWPTU" />
<Items Item="K5D103BMA001" />
<Items Item="K5D502BNA007" />
<Items Item="THTF011N " />
<Items Item="TMM2A19005-1" />
<Items Item="TMM2AA19005-1" />
<Items Item="TMM2AD00701" />
<Items Item="TMM2AD00801" />
<Items Item="TMM2AD00901" />
<Items Item="TMM6463-1" />
<Items Item="T4EX216005" />
<Items Item="TYZQF014" />
</Models>
</ModelCategory>



This is my Query


--------------------------------------------------------- Header Model Category
SELECT [model category],
(----------------------------------------------------------- Model
SELECT Model,
(------------------------------------------------------ Item
SELECT Item
FROM TBL_BOM Items WITH (nolock)
WHERE
Items.model = Models.model and
Items.[model category] = ModelCategory.[model category]
GROUP BY Item, model, [model category]
FOR XML AUTO, TYPE--, ELEMENTS
)--------------------------------------------------- End Item
FROM TBL_BOM Models WITH (nolock)
WHERE Models.[model category] = ModelCategory.[model category]
GROUP BY Model, [model category]
FOR XML AUTO, TYPE--, ELEMENTS
)------------------------------------------------------------ End Model
FROM tbl_bom ModelCategory WITH (nolock)
GROUP BY [model category]
ORDER BY [model category]
FOR XML AUTO, TYPE
------------------------------------------------------ End Header Model Category

any idea

 

[tsuji]

[0] The way you mention the new requirement is actually not exactly correct. What you showed with tabbing level correcponding to the explosion level, though is not absolutely meaningless, is putting significant information at the place not commensurate to its importance. But, I can see now what you actually mean to get. It is parent-child relation rather than tabbing more and more. (You have to see the output yourself below to see what I mean, maybe.)

[1] A pure sql solution though possible would be tedious because it is a top-down logic and it is now informed how many level deep it should anticipate, so all level-related info is hard scripted. If there are many levels, like 5 levels here, it is very very clumsy.

[2] I only write out the pure sql solution for two levels. You have to successively exploding level 2 to including 3, then 4, then 5 successively.
[tt]
SELECT [model category],
(
SELECT Model,
(
SELECT Item[blue],[/blue]
[blue](
SELECT Item
FROM TBL_BOM Items_2 WITH (nolock)
WHERE
Items_2.[Explosion Level]=2 and
Items_2.[Parent Item]=Items_1.[Parent Item] and
Items_2.model = Models.model and
Items_2.[model category] = ModelCategory.[model category]
GROUP BY [Explosion Level], [Parent Item], Item, model, [model category]
FOR XML AUTO, TYPE
)[/blue]
FROM TBL_BOM Items[blue]_1[/blue] WITH (nolock)
WHERE
[blue]Items_1.[Explosion Level] = 1 and[/blue]
Items[blue]_1[/blue].model = Models.model and
Items[blue]_1[/blue].[model category] = ModelCategory.[model category]
GROUP BY Item, model, [model category]
FOR XML AUTO, TYPE--, ELEMENTS
)--------------------------------------------------- End Item
FROM TBL_BOM Models WITH (nolock)
WHERE Models.[model category] = ModelCategory.[model category]
GROUP BY Model, [model category]
FOR XML AUTO, TYPE--, ELEMENTS
)------------------------------------------------------------ End Model
FROM tbl_bom ModelCategory WITH (nolock)
GROUP BY [model category]
ORDER BY [model category]
FOR XML AUTO, TYPE
------------------------------------------------------ End Header Model Category
[/tt]
[3] A seemingly minor point: why do you find it clearer to use column name with space inside? I don't think it is very helpful both in clarity and programming.

[4] Hence, for a couple of level deep, the sql is always exploding rapidly. A more manageable and dynamic (level of explosion) db-solution might be found prabably in a stored procedure approach.

[5] Another alternative, more related to the subject of this forum would be to use your original flat items output, adding "parent item" as a minimum in the selected field. The output xml then submitted to an xsl-transformation to make it nested deeper according to the explosion level. But that would be another story all together.

 

[carloshgogmail]

Thanks

first two points i agreed with you
3 point this is how the table was created by another person
and also agreed with the rest of your points, im looking something more dinamically or nested to do this, the key is on the table fields (Explosion Num, Explosion Level) but still trying to figure out how to solve this with an easy and nice query maybe imposible, i believe would be easier to read the table and fill a tree on vb net and maybe create the xml from the tree.