what is the best way to... - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

what is the best way to...

Author

Topic

rogdawg
Starting Member

23 Posts

Posted - 2008-03-28 : 10:56:08

I need to display hierarchical XML data from my SQL database. I have written the stored procedure that recurses through the data and outputs XML. Everything works fine. I can run the procedure and get the data. The problem is, it can take almost 20 seconds to run the procedure, as it recurses through all the data.

Right now, I manually go to the database and run the procedure when the data has changed, save the output to a file, and post the file to a website which displays the data in a tree view control.

This all needs to be dynamic.

So, how do I automatically run the procedure and save the output to a file whenever an edit is made to the data table? This sounds like something I could use a trigger to do, I just can't find any examples of how to SAVE the output to file, once my stored procedure is triggered. Can someone point me to an example, or tutorial?

Or...can I have a VIEW that is in XML form? I don't believe this is possible, but it seems like it would be the most efficient answer, if it was. I have experimented with creating a view that is the result of a recursive CTE, and that works ok but, I still wind up with a "flat" table, that has to be recursed through to create XML so, I don't think I have gained anything by this approach.

Thanks, in advance, for any advice you can give, or any links to tutorials or articles that might help.

Thanks again!
rogdawg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 11:01:47

Ok, some things first.

1) How do the original source data look like?
2) How do the XML content look like?

Also read and understand this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

rogdawg
Starting Member

23 Posts

Posted - 2008-03-28 : 11:32:50

Thank you for your response.

I am sorry. I didn't include data descriptions, because I think I have a handle on all that, I am just trying to figure out the best way to do the output (save to file, or somehow use a view). But, the data will look like this:
Source Data:
CREATE TABLE objects(ID int, TITLE nvarchar(255), PARENT_ID int, CLASS_ID int)

SELECT 0, 'The Original Object', -1, 1 UNION ALL
SELECT 3, 'A second-generation object', 0, 3 UNION ALL
SELECT 5, 'Another second-generation object', 0, 2 UNION ALL
SELECT 17, 'A third-generation object', 3, 3 UNION ALL

XML:
<Object id="0" title="The Original Object" parentID="-1" clsID="1">
<Object id="3" title="A second-generation object" parentID="0" clsID="3">
<Object id="17" title="A third-generation object" parentID="3" clsID="3" />
</object>
<Object id="5" title="Another second-generation object" parentID="0" clsID="2">
</object>
</Object>

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:04:31

You can certainly have cte in a view.

DECLARE	@Sample TABLE
	(
		ID INT,
		TITLE NVARCHAR(255),
		PARENT_ID INT,
		CLASS_ID INT
	)

INSERT	@Sample
SELECT	0, 'The Original Object', -1, 1 UNION ALL
SELECT	3, 'A second-generation object', 0, 3 UNION ALL
SELECT	5, 'Another second-generation object', 0, 2 UNION ALL
SELECT	17, 'A third-generation object', 3, 3

;WITH Yak
AS (
	SELECT		s1.ID AS [@ID],
			s1.TITLE AS [@Title],
			s1.PARENT_ID AS [@parentID],
			s1.CLASS_ID AS [@clsID],
			CAST(s1.ID AS VARCHAR(MAX)) AS [Path]
	FROM		@Sample AS s1
	WHERE		s1.PARENT_ID < 0

	UNION ALL

	SELECT		s1.ID,
			s1.TITLE,
			s1.PARENT_ID,
			s1.CLASS_ID,
			y.Path + '/' + CAST(s1.ID AS VARCHAR(MAX))
	FROM		@Sample AS s1
	INNER JOIN	Yak AS y ON y.[@ID] = s1.PARENT_ID
)

SELECT		[@ID],
		[@Title],
		[@parentID],
		[@clsID]
FROM		Yak
ORDER BY	Path
FOR XML		PATH('Object'),
		ROOT('XML')

E 12°55'05.25"
N 56°04'39.16"

rogdawg
Starting Member

23 Posts

Posted - 2008-03-28 : 12:57:56

Thank you so much!

I don't know why I have not been able to find any examples of a view "returning" XML data but, that is exactly what I have been looking for!

Fantastic.

Thank you.

rogdawg
Starting Member

23 Posts

Posted - 2008-03-28 : 14:28:00

Peso,

If I run the query that you posted as it is, it works just fine but, if I put it in a CREATE VIEW statement, I get an error stating "Create View or Function failed because no column name was specified for column 1"

So, at the line:
; WITH YAK
or, at its equivelant in my code, I tried to create a column for the view like:
; WITH YAK(result)

thinking that the XML could be returned in this column but, when I attempt this I get this error:
"'YAK' has more columns than were specified in the column list."

SO, I think I am back to my original question...can a view "return" XML data, and if so, what is the syntax for that?

Thanks again for your time, and your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources