Creating xml files from xml file

Transact-SQL
1

xml file

Orig XML.jpg1192×323 106 KB

So far I have managed to get to
SELECT
h.c.value('@supplierref[1]' , 'VARCHAR(6)') AS supplierref,
h.c.value('@suppliername[1]' , 'VARCHAR(50)') AS suppliername
FROM ( SELECT XMLTEXT AS Xmlreport FROM @WORKINGXML) d
CROSS APPLY Xmlreport.nodes('//Remittance') AS a(c)
OUTER APPLY a.c.nodes('supplier') h(c)
OUTER APPLY a.c.nodes('faxnumber') s(c)

which gives me the supplierref and supplier name. what I want to do is also get the xml remittance for that supplier so I can add supplierref, supppliername and xml to a table

ie

supplierref = 'ABCDE' , SupplierNAme = 'SUPPLIER 1' and xml to equel (tags removed to post )

Remittances
Remittance
supplier supplierref="ABCDE" suppliername="SUPPLIER 1" /
faxnumber faxnumber="12345 456789" /
remittancedate RemittanceDate="05/01/2017" paymentdate="09/01/2017 00:00" /
address address-1="" address-2="" address-3="" address-4="" address-5="" /
remittance remitlinedate="30-11-2016" remitlineourref="0000222" remitlinedoctype="INVOICE" remitlineyourref="Ref123" itemvalue="336.60" /
/Remittance
/Remittances

2

So my question to you is, where is this xml file? Or do you mean this data is currently saved in an xml datatype column? and do you want to dump the result our to an xml file or an xml result using TSQL?

3

the xml file is stored in a crm multiline field. I would like it dumped as an xml result as it is part of a stored procedure returning results back to a crm workflow

4

yes but what is the source of this data, is it sql server database table? and you want it to be dumped to disk? the crm must be getting it from somewhere to display it in a UI multiline field

5

its getting it from a sql database table. dumping it to disc will be great, I can work with it from there

6

you have a couple of options depending on your setup. Is this going to b a scheduled process or a one time thing?

  1. bcp from a command line either via a bat script file or powershell
  2. SSIS packages

Which one are you more comfortable with?

What is it you are attempting to do, give us a general idea of what you are attempting to do

7

basically what I am trying to do is ;

I receive a text file from a customer with remittance advices. This gets converted to an xml file and stored within CRM ( sql database ) . When the file is uploaded into CRM, the CRM system will fire a custom workflow ( the part I'm working on ), that hopefully will call a stored procedure and create the individual supplier remittances xml data that will be returned to the workflow for processing. I have used xml files before with stored procedures and normal just return a table from a Select command
ie in this instance from a stored procedure id like to return

supplierref suppliername, xml data
ABCDE SUPPLIER 1 xml data ( as shown above )

if that's not possible, the to be able to create an xml file for each supplier's remittance to disc will work just as well as I can read that back into the workflow.

8

Based on the description in your last post:

select 
	c.value('supplier[1]/@supplierref', 'varchar(6)') AS supplierref,
	c.value('supplier[1]/@suppliername[1]' , 'VARCHAR(50)') AS suppliername,
	c.query('.') as RemittanceXML
from
	@WORKINGXML.nodes('//Remittance') T(c)
9

Brilliant James,
That's perfect. Thank you for your help , and education