New answers tagged xml
I had this same issue when google geocoding and parsing the XML result. Seems that when run via an SQL job the XML result was being truncated. Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html Had to put: SET TEXTSIZE 2147483647; at the start of my stored proc.
For OLE automation stored procedures you need to run as a user that's part of the sysadmin server role. I would venture to guess that currently it fails and doesn't form a correct XML. Run the job step as a different user and see if that fixes the problem. From BOL: Permissions Requires membership in the sysadmin fixed server role.
I took the liberty of assuming that you want the XML to look like this: <row> ... <practices> <practice> <ID>1</ID> <Name>test1</Name> </practice> <practice> <ID>2</ID> <Name>test2</Name> </practice> <practice> <ID>3</ID> ...
You need to remove the / at the start of the value() function. i.e. this one Listing.value('(/. You also need to remove the CROSS JOIN that occurs because you are using old style JOIN syntax between ListingTable(Listing) and TempTable(ReportData). You can do that by referencing the PCMReportData slightly differently. Based on your XML schema, you should ...
You don't need to SET the XMLRESULT. You should pass it in as an OUTPUT parameter. https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx DECLARE @XMLRESULT xml; EXEC usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName ='GenRepot', @XMLResult = @XMLRESULT OUTPUT; You can then use the @XMLRESULT parameter, which has been ...
This appears to be a duplicate of this question: Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use? However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question. There is no obvious choice ...
Top 50 recent answers are included