-1

I have been searching on the net for the last couple of days and unfortunately cannot find quite what I am looking for. I have a SQL Server 2012 table that contains 2 columns, one is PurchaseOrder DataType varchar(10) the other is Data with a DataType of XML. Currently what is happening ever few hours a stored procedure is run to check a number of table for a value and then create a PurchaseOrder in the desired XML format. This has been achieved and validated by the company that I am going to be sending the data to. The issue I now have is I want to create a SSIS package that looks at this table and creates multiple xml files with the contents of the Data field "not putting the contents in to one long line" and then using the PurchaseOrder field for the file name. I have found various part solutions e.g. using a Script task as there is no native XML Destination in SSIS but struggling and being quite new to SSIS not getting anywhere fast. Can anyone offer any help, guidance, youtube links or even a part way there package.

Thanks P

Further to the above question I have tried to go about it using the bcp command, below is the following code that works great just looking at the top 1 record 

DECLARE @fileName VARCHAR(50)

DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

SET @fileName = 'C:\Temp\test.xml'
SET @sqlStr = 'select TOP 1 Data from DatabaseName.dbo.OutputXML'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + '
-w -T -S Server\Instance'

EXEC xp_cmdshell @sqlCmd

What I have then done is add this in to a cursor, which again works to a point, it creates my files, using the purchaseorder as the file name and outputs the relevant information but it no longer holds the proper xml format, it goes back to looking like a string with none of the original formatting, is this because I am imbedding it in the cursor?? Below is the code that I am using 

DECLARE @fileName VARCHAR(50) 
DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)

DECLARE xml_cursor CURSOR FOR

SELECT PurchaseOrder from DatabaseName.dbo.OutputXML

OPEN xml_cursor

FETCH NEXT FROM xml_cursor INTO @fileName

WHILE @@FETCH_STATUS = 0 
BEGIN

SET @fileName = 'C:\Temp\'+@fileName+'.xml'
SET @sqlStr = 'select Data from DatabaseName.dbo.OutputXML'

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' 
-w -T -S Server\Instance'

EXEC xp_cmdshell @sqlCmd

FETCH NEXT FROM xml_cursor into @fileName

END
CLOSE xml_cursor
DEALLOCATE xml_cursor
PJD
  • 743
  • 2
  • 12
  • 38
  • The only thing you use your cursor for is the filename, you will need to filter your output by fetching the key and adding it to your @sqlStr as well. Otherwise each file will contain all the records. Remember, BCP is a command line utility with no regard for your cursor – Jasper Schellingerhout Sep 02 '15 at 17:05

1 Answers1

2

Since bcp is a command line utility it does not know about your cursor. You can only change the parameters passed. You already pass the @filename from the cursor, you will also need to filter your query. Below is how to fix your problem but it is far from a great solution, it will be quite slow. I don't know what your key is called, so I called it SomeKey

DECLARE @fileName VARCHAR(50) 
DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000)
DECLARE @SomeKey VARCHAR(100) --Put your key datatype here 

DECLARE xml_cursor CURSOR FOR

-- replace SomeKey with your key
SELECT SomeKey,PurchaseOrder FROM DatabaseName.dbo.OutputXML

OPEN xml_cursor

FETCH NEXT FROM xml_cursor INTO @SomeKey,@fileName

WHILE @@FETCH_STATUS = 0 
BEGIN

SET @fileName = 'C:\Temp\' + @filename + '.xml'
SET @sqlStr = 'SELECT Data FROM DatabaseName.dbo.OutputXML WHERE SomeKey = ''' + @SomeKey +''''

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + '-w -T -S Server\Instance'

EXEC xp_cmdshell @sqlCmd

FETCH NEXT FROM xml_cursor into @SomeKey,@fileName

END
CLOSE xml_cursor
DEALLOCATE xml_cursor
Jasper Schellingerhout
  • 1,070
  • 1
  • 6
  • 24