Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Cannot save XML query to a file using xp_cmdshell

I have a query that exports resumes to an XML file. The query has been tested and runs without error and produces the XML file as requested by my client. I need to be able to send the results to a file, though, as the final run will have over 500,000 resumes. However, when I put the query in a string and send it to the xp_cmdshell command line, it does not do anything. It does not even send an error. I get a return with parameter information. However, I know the parameters are correct, since when I run the command on a simpler query, it works and the file is created. Also, selecting the string for the XML query and running that, returns the proper results. I do not know, therefore, what is wrong.

Here is the complete sql:

DECLARE @cmd nvarchar(4000), @FileOut nvarchar(200)
SELECT  @FileOut = 'G:\Resumes\XML\Binary5.xml'
SELECT @cmd = 'bcp ' +
'"DECLARE @soapHeader VARCHAR(1000)
DECLARE @soapFooter VARCHAR(1000)
DECLARE @JobSeeker VARCHAR(1000)
DECLARE @Resumes xml
SET @soapHeader=
''<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
  <SOAP-ENV:Header>
    <mh:MonsterHeader xmlns:mh="http://schemas.monster.com/MonsterHeader">
      <mh:MessageData>
        <mh:MessageId>RCI AAS Resumes</mh:MessageId>
        <mh:Timestamp>'+CONVERT(varchar(20),getdate())+'</mh:Timestamp>
      </mh:MessageData>
      <mh:ProcessingReceiptRequest>
        <mh:Address transportType="http">http</mh:Address>
      </mh:ProcessingReceiptRequest>
    </mh:MonsterHeader>
    <wsse:Security xmlns:wsse="http://schemas.xmlsoap.org/ws/2002/04/secext">
      <wsse:UsernameToken>
        <wsse:Username>miteparallon</wsse:Username>
        <wsse:Password>mEzNFl@0</wsse:Password>
      </wsse:UsernameToken>
    </wsse:Security>
  </SOAP-ENV:Header>
  <SOAP-ENV:Body>''
SET @JobSeeker=            ''<JobSeekers xsi:schemaLocation="http://schemas.monster.com/Monster http://schemas.monster.com/current/xsd/JobSeeker.xsd" xmlns="http://schemas.monster.com/Monster" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">''
SET @soapFooter=
''      </JobSeekers>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>'''+
' SELECT CAST(@soapHeader+@JobSeeker+
(SELECT
''addOrUpdate'' AS ''@seekerAction'',
a.Resume_ID AS ''@seekerRefCode'',
''active'' AS ''@status'',
''6722'' AS ''Channel/@monsterId'',
a.FName AS ''PersonalData/Contact/StructuredName/GivenName'',
a.LName AS ''PersonalData/Contact/StructuredName/FamilyName'',
a.Address AS ''PersonalData/Contact/Address/StreetAddress'',
a.Address2 AS ''PersonalData/Contact/Address/StreetAddress2'',
a.City AS ''PersonalData/Contact/Address/City'',  
REPLACE(a.State,''US-'','''') AS ''PersonalData/Contact/Address/State'',
a.Country AS ''PersonalData/Contact/Address/CountryCode'',
a.Zip AS ''PersonalData/Contact/Address/PostalCode'',
''home'' AS ''PersonalData/Contact/Phones/Phone/@phoneType'',
a.Phone AS ''PersonalData/Contact/Phones/Phone'',
a.Email AS ''PersonalData/Contact/E-mail'',
''addOrUpdate'' AS ''Resumes/Resume/@resumeAction'',
SUBSTRING(RTRIM(LTRIM(a.Resume_ID)),1,200) AS ''Resumes/Resume/@resumeRefCode'',
''7790'' AS ''Resumes/Resume/BoardName/@monsterId'',
a.DateModified AS ''Resumes/Resume/ResumeModDate'',
''add'' AS ''Resumes/Resume/ResumeDocument/@documentAction'',
d.OrigDoc AS ''Resumes/Resume/ResumeDocument/File'',
d.FileName AS ''Resumes/Resume/ResumeDocument/FileName''
FROM Resumes a
LEFT OUTER JOIN ResReferrer r on r.ResID = a.Resume_ID
LEFT OUTER JOIN _Referrers b on b.RefID = r.RefID
INNER JOIN (
SELECT DocID, ResID, OrigDoc, FileName, ROW_NUMBER() OVER (ORDER BY ResID DESC) AS RowNumber
FROM ResDocuments
) d on d.ResID = a.Resume_ID
WHERE LEN(a.Email) > 0 AND a.State LIKE ''US-%''
AND a.Zip IN (SELECT ZipCode FROM ats_Master.dbo.USZipCodes)
AND a.Email LIKE ''%@%.%''
AND DATEADD(yyyy,-3,getdate()) < DateModified
AND d.RowNumber BETWEEN 1001 AND 1500
FOR XML PATH (''JobSeeker''),
ELEMENTS )+@soapFooter
AS XML)"' +' queryout '+@FileOut+' -w -T -S ' + @@SERVERNAME

--SELECT @cmd
EXECUTE master..xp_cmdshell @cmd

Again, if I uncomment the SELECT statement, I get a valid SQL query that runs perfectly. If I change @cmd to a simpler query, cmdshell creates the file.
0
mfkaminski
Asked:
mfkaminski
1 Solution
 
mfkaminskiAuthor Commented:
OK, I got this working. You need to put the entire XML query into a stored procedure and then run that as the bcp command.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now