Viewing XML output after query is executed

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)

Viewing XML output after query is executed

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-01-29 : 11:17:37

Hi everyone!

I'm new to this forum and sql server. Not sure if I'm posting this in the right place (I apologize if I am posting this in the wrong place!). I tried doing a search for the answer first, but couldn't the correct result.

I am writing a procedure to extract data and put it into xml format. I will be using this xml document as a daily content feed to a certain website.

I have written the procedure, and it executes ok, but I don't know how to view the results to see if the structure of the xml document is correct. After a query is executed and an xml document is generated, where is it persisted? Or, is it even persisted? How can I access this xml document to view it and check to see if the query produced the correct results?

Thank you in advance,

cc

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-29 : 12:01:24

Can you post what you are executing.
How are you creating the file? Are you specifying a file name anywhere?

I usually format the xml in a stored proc and use bcp to create the file - in that way I can look at the format in query analyser without going to the disk.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-01-29 : 12:11:07

This is the basics. I am making modifications, so this may not execute correctly, but I still don't know how to display the xml document when this procedure does execute.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[Submission]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SELECT 1 as Tag, NULL as Parent,
	s.AGENT_ID	as [Store!1!id],
	s.AGENT_FIRSTNAME as [Store!1!name],
	NULL		as [Sale!2!orderno],
	NULL		as [Sale!2!1ty],
	NULL		as [Discount!3!type],
	NULL		as [Discount!3!lowqty],
	NULL		as [Discount!3!highqty],
	NULL		as [Discount!3!amount!element]
FROM T_AGENT s

UNION ALL

SELECT 2, 1,
	s.AGENT_ID,
	s.AGENT_FIRSTNAME,
	sa.PROP_CITY,
	sa.PROP_ZIPCODE,
	NULL,
	NULL,
	NULL,
	NULL
FROM T_AGENT s, T_PROP sa
WHERE s.AGENT_ID = sa.PROP_ID

UNION ALL

SELECT 3, 1,
	NULL,
	s.AGENT_ID,
	NULL,
	NULL,
	sa.PROP_STREETNUM,
	sa.PROP_STREETNAME,
	sa.PROP_CITY,
	sa.PROP_ZIPCODE
FROM T_AGENT s, T_PROP sa
WHERE s.AGENT_ID = sa.PROP_ID

ORDER BY [store!1!name]

For XML EXPLICIT

END

Kristen
Test

22859 Posts

Posted - 2006-01-29 : 12:32:49

You can run a FOR XML EXPLICIT query in Query Analyser (for example) but it will contain spurious line breaks (e.g. at every 4000 character boundary).

To actual get the XML out you need to be using a "Stream", rather than a conventional Record Set.

You can get the data out using BCP - but you will get benign warning messages from BCP [about length overrun IIRC]

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-29 : 12:57:21

That's just a query. The output destination will be decided by how it is executd.
If you execute in query analyser the output will be to the result pane (unless you set to a file).
bcp/osql will output to a file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-01-29 : 15:48:25

Kristen and nr, thank you for your reponses.

I've never used bcp. Would this be an additional command in the query? If so, could you give me an example what this command would look like (if it's a simple command).

ty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-29 : 18:44:37

bcp/osql is command line utlity. You execute this from the command prompt or you can also do it via xp_cmdshell. Refer to Books Online for detail syntax and usage

----------------------------------
'KH'

my_aro
Yak Posting Veteran

50 Posts

Posted - 2006-01-30 : 01:56:58

i have no idea if it can be done in any sql gui apps / utility tools of mssql server since im a developer and not really well versed on mssql, but 1 thing im sure, u can have some DTS ActiveX Script Task. Surely a simple visual basic activeX script can help you with this.. together with MSXML 4.0 installed in your local machine..

Function Main()

Set objectDom = CreateObject("Msxml2.DOMDocument.4.0")

Set objectCmd = CreateObject("ADODB.Command")
objectCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=XXXX;Initial Catalog=db;UID=XXXX;Password=XXXX"

someSQL = "<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"><sql:query>" & _
"<an sql query> for xml auto</sql:query></ROOT>"

objectCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
objectCmd.CommandText = someSQL

objectCmd.Properties("Output Stream") = objectDom
objectCmd.Execute , , 1024

objectDom.Save "<%directory_home>\name.xml"

Main = DTSTaskExecResult_Success

End Function

//note: just fill in your own values

this time u have created an xml file, then simply run the xml file to view..

hope this helps..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources