Add parent tag to XML Explicit

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Add parent tag to XML Explicit

Author

Topic

daz_oldham
Starting Member

5 Posts

Posted - 2005-08-11 : 12:04:27

Hello

No-brains again! (lol)

I've been trying to fathom out how to add a parent tag to an xml explicit statement, and was wondering if someone could help please?

Here's my SQL

set nocount on

SELECT
1 As Tag, NULL as Parent,

AttributeStructureID AS [ProductAttribute!1!AttributeStructureID],
Name AS [ProductAttribute!1!AttributeName!element],
Specification AS [ProductAttribute!1!AttributeValue!element]

FROM V_ProductAttributeSpecifications ProductAttribute WHERE ProductID = 2464 ORDER BY [ProductAttribute!1!AttributeStructureID] ASC for XML EXPLICIT

Thanks in advance

Daz

-----------------
Daz

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-12 : 09:32:40

Hi Daz
You need to union in a dummy resultset. I.e.:

SELECT
	1 AS Tag, 
	NULL AS Parent, 
	NULL AS [ProductAttributes!1], 
	NULL AS [ProductAttribute!2!AttributeStructureID],
	NULL AS [ProductAttribute!2!AttributeName!element],
	NULL AS [ProductAttribute!2!AttributeValue!element]
UNION SELECT
	2 AS Tag, 
	1 AS Parent,
	NULL AS [ProductAttributes!1], 	
	AttributeStructureID AS [ProductAttribute!2!AttributeStructureID],
	[Name] AS [ProductAttribute!2!AttributeName!element],
	Specification AS [ProductAttribute!2!AttributeValue!element]
FROM 
	V_ProductAttributeSpecifications ProductAttribute 
WHERE 
	ProductID = 2464 
ORDER BY 
	[ProductAttribute!2!AttributeStructureID] ASC 
FOR XML EXPLICIT

Mark

daz_oldham
Starting Member

5 Posts

Posted - 2005-08-15 : 05:13:06

That's great - thanks Mark :)

-----------------
Daz

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-15 : 07:26:36

no problem!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources