Writing XML to C: Drive - 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

General SQL Server Forums

New to SQL Server Programming

Writing XML to C: Drive

Author

Topic

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 09:54:26

Is there a way to modify this code to write this file directly to my C drive as an XML file? I'm having difficulty retrieving the file due to its size, so I'm hoping that if I can write it to my C: I can circumvent pulling the file within MSSMS and having the system crash due to the file size.

SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + ', '
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID
FOR XML PATH('')),
DT.DocumentText
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH ('Doc')

Any help is much appreciated, I'm a bit new to this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 09:59:33

use bcp


EXEC xp_cmdshell 'bcp "SELECT 
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + ', '
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID 
FOR XML PATH('''')),
DT.DocumentText 
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH (''Doc'')" queryout C:\...pathname\filename.xml  -Sservername -T -c'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 10:24:09

Thanks so much, apologies for my naivety, I'm very new to this. I have a couple questions about the query. Below is how I modified the string to my machine, and these are the errors I'm getting.

EXEC master xp_cmdshell 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + ', '
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID
FOR XML PATH('')),
DT.DocumentText
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH ('Doc')" queryout C:\SQLXML\test.xml -S. -T -c'

I changed the server name after -S to . because it's a local machine, I'm not sure if this is correct. This is the error message I'm getting.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'.

Again, apologies for my naivety, your help is very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 10:33:56

use this modified suggestion. also give machine name rather than .


EXEC master xp_cmdshell 'bcp "SELECT 
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + '', ''
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID 
FOR XML PATH('''')),
DT.DocumentText 
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -Sput your server name here -T -c'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 10:39:12

Does it matter what I name it? Thank you so much btw, you're really helping me out of a jam.

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 10:40:35

It's giving me this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'

Am I doing something wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 10:42:00

quote:
Originally posted by Jgries

It's giving me this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'

Am I doing something wrong?

please copy paste my suggestion and then add server name. I've made some changes to 's appearing inside to escape them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 10:42:33

quote:
Originally posted by Jgries

Does it matter what I name it? Thank you so much btw, you're really helping me out of a jam.

it should be same as your server name and cant be just .

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 12:16:24

Still getting an error, here is my input, and here's my output. I'm copy pasting your revisions.

EXEC master xp_cmdshell 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + '', ''
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID
FOR XML PATH('''')),
DT.DocumentText
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -SJGRIES-SP-5139 -T -c'

------
Error Message
------

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'.

I'm using MSSMS 2008 if that makes any difference. Again, thank you for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 12:23:01

there should be space between -S and servername

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 12:25:44

Added a space, still giving me a syntax error?

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 12:53:33

try this too


DECLARE @Cmd varchar(8000)
SET @Cmd='bcp "SELECT P.ProjectName AS [@ProjectName],P.ProjectCity AS [@ProjectCity],P.ProjectState AS [@ProjectState],[@ArchName] = (SELECT CompanyName + '', '' FROM dbo.Company AS C WHERE C.ProjectID =  ProjectID FOR XML PATH('''')),DT.DocumentText FROM dbo.DocumentText AS DT JOIN dbo.DocumentProperty AS DP ON DT.DocumentPropertyID = DP.DocumentPropertyID JOIN dbo.Project AS P ON DP.ProjectID = P.ProjectID FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -S JGRIES-SP-5139 -T -c'
EXEC xp_cmdshell @Cmd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 12:59:57

I'm sorry, I don't know what the issue seems to be. It's giving me this same syntax error. I can't fathom what it might be.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName]'.

Current Query
----------------------
DECLARE @Cmd varchar(8000)
SET @Cmd='bcp "SELECT P.ProjectName AS [@ProjectName],P.ProjectCity AS [@ProjectCity],P.ProjectState AS [@ProjectState],[@ArchName] = (SELECT CompanyName + '', '' FROM dbo.Company AS C WHERE C.ProjectID = ProjectID FOR XML PATH('''')),DT.DocumentText FROM dbo.DocumentText AS DT JOIN dbo.DocumentProperty AS DP ON DT.DocumentPropertyID = DP.DocumentPropertyID JOIN dbo.Project AS P ON DP.ProjectID = P.ProjectID FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -S JGRIES-SP-5139 -T -c'
EXEC xp_cmdshell @Cmd

EXEC master xp_cmdshell 'bcp "SELECT
P.ProjectName AS [@ProjectName],
P.ProjectCity AS [@ProjectCity],
P.ProjectState AS [@ProjectState],
[@ArchName] = (SELECT CompanyName + '', ''
FROM dbo.Company AS C
WHERE C.ProjectID = P.ProjectID
FOR XML PATH('''')),
DT.DocumentText
FROM dbo.DocumentText AS DT
JOIN dbo.DocumentProperty AS DP
ON DT.DocumentPropertyID = DP.DocumentPropertyID
JOIN dbo.Project AS P
ON DP.ProjectID = P.ProjectID
FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -S JGRIES-SP-5139 -T -c'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 13:08:49

nope. run only this part

DECLARE @Cmd varchar(8000)
SET @Cmd='bcp "SELECT P.ProjectName AS [@ProjectName],P.ProjectCity AS [@ProjectCity],P.ProjectState AS [@ProjectState],[@ArchName] = (SELECT CompanyName + '', '' FROM dbo.Company AS C WHERE C.ProjectID = ProjectID FOR XML PATH('''')),DT.DocumentText FROM dbo.DocumentText AS DT JOIN dbo.DocumentProperty AS DP ON DT.DocumentPropertyID = DP.DocumentPropertyID JOIN dbo.Project AS P ON DP.ProjectID = P.ProjectID FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -S JGRIES-SP-5139 -T -c'
EXEC xp_cmdshell @Cmd

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 13:59:23

Ok, it seems to be executing correctly, but there's no file in my folder. It appears part of the statement isn't generating as its all in red. This is the portion that's in red.

'bcp "SELECT P.ProjectName AS [@ProjectName],P.ProjectCity AS [@ProjectCity],P.ProjectState AS [@ProjectState],[@ArchName] = (SELECT CompanyName + '', '' FROM dbo.Company AS C WHERE C.ProjectID = ProjectID FOR XML PATH('''')),DT.DocumentText FROM dbo.DocumentText AS DT JOIN dbo.DocumentProperty AS DP ON DT.DocumentPropertyID = DP.DocumentPropertyID JOIN dbo.Project AS P ON DP.ProjectID = P.ProjectID FOR XML PATH (''Doc'')" queryout C:\SQLXML\test.xml -S JGRIES-SP-5139 -T -c'

Jgries
Starting Member

17 Posts

Posted - 2011-12-02 : 14:21:46

I'm getting this error on the results tab.

SQLState = S0002, NativeError = 208
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'dbo.DocumentText'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.
NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-03 : 00:20:46

ok. that means it cant find the table in database
can you make it [your database name].dbo.DocumentText and try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources