[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

save to external XML file

Hi I have an xml file read and do some modification using sql code and puts out put in @content variable but i need to save the same @content variable contents  as xml file using bcp command as an external xml file
0
ModSpace
Asked:
ModSpace
  • 11
  • 9
3 Solutions
 
ModSpaceAuthor Commented:
Please let me know-thanks
0
 
Kevin CrossChief Technology OfficerCommented:
Did my article link not help you? http://daytabase.org/2011/08/20/bcp-utility/
Or what questions do you have on applying it to your specific scenario? Please post what you have tried and results, i.e., error message or disposition of file, etc. Note that if you are using bcp Utility from within TSQL that it requires the xp_cmdshell be enabled.
0
 
ModSpaceAuthor Commented:
SET @bcpcmd = 'bcp' +  '@content' + 'queryout'+ 'format nul'+ '-x -f'+ 'C:\Temp\\InvoiceExtractResult.xml'+ '-n-T';
SELECT @bcpcmd AS 'Command to execute'

EXEC master..xp_cmdshell @bcpcmd;

The filename, directory name, or volume label syntax is incorrect.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
ModSpaceAuthor Commented:
SET @bcpcmd = 'bcp' +  '@content' + 'queryout'+ 'format nul'+ '-x -f'+ 'C:\Temp\InvoiceExtractResult.xml'+ '-n-T'; --correct one
0
 
ModSpaceAuthor Commented:
Yes I enabled xp_cmdshell.
0
 
Kevin CrossChief Technology OfficerCommented:
So @content is same as in this PAQ'd question, right? I will take a look at your bcp attempt with that example.
0
 
ModSpaceAuthor Commented:
Yes it's PAQ'd question.
0
 
Kevin CrossChief Technology OfficerCommented:
Additionally, before going too far into this, I notice that you have @content directly in the string. It won't work like that. What you are sending is literal '@content' and not the value of that variable. Using queryout, you need to have a query so are really needing to SELECT the contents. If you are going to be doing this often, I would recommend creating a table as I suggested:

CREATE TABLE xmlfeeds(
   xml_id NUMERIC(18,0) IDENTITY(1,1),
   xml_date DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
   xml_data XML NOT NULL,
   PRIMARY KEY(xml_id)
);

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If you go that route, the updated code looks like this:

INSERT INTO xmlfeeds(xml_data)
VALUES(@content);


DECLARE @xmlid NUMERIC(18,0),
        @bcpcmd VARCHAR(2000),
        @filename VARCHAR(50);

/* Gets auto-increment identity value as last INSERT in session. */
SET @xmlid = SCOPE_IDENTITY();
SET @filename = 'C:\Temp\InvoiceExtractResult.xml';

SET @bcpcmd = 'bcp "SELECT xml_data FROM YourDatabase..xmlfeeds WHERE xml_id ='+CONVERT(VARCHAR(18),@xmlid)+'" queryout "'+@filename+'" -T -c';
/* Using xp_cmdshell, execute bcp command. */
EXEC master..xp_cmdshell @bcpcmd;

If not, then you will probably have to use sp_executesql and pass in @content as a parameter.
0
 
ModSpaceAuthor Commented:
Will it be possible to do it directly not using any table and save the ouput(@content) to the xml file.
0
 
Kevin CrossChief Technology OfficerCommented:
Look at sp_executesql as I said then. :)
If your XML is guaranteed to be small, you can try:

SET @bcpcmd = 'bcp "SELECT ''' + CONVERT(VARCHAR(2000), @content) + ''';" queryout "'+@filename+'" -T -c';

Whenever I have tried with VARCHAR(MAX) it errors out bcp, but you can give it a shot. :)
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, do not ask me what is different, but I tried VARCHAR(MAX) again for giggles and it worked.
DECLARE @bcpcmd VARCHAR(2000),
        @filename VARCHAR(50);

SET @filename = 'C:\Temp\InvoiceExtractResult.xml';

SET @bcpcmd = 'bcp "SELECT ''' + CONVERT(VARCHAR(MAX), @content) + ''';" queryout "'+@filename+'" -T -c';
/* Using xp_cmdshell, execute bcp command. */

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

EXEC master..xp_cmdshell @bcpcmd;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

Open in new window

0
 
ModSpaceAuthor Commented:
Thanks for the update and i will see other options. you can close this for now.
0
 
ModSpaceAuthor Commented:
Thanks
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome. I remember, xp_cmdshell has a limit of VARCHAR(8000) anyway, so you can change both the @bcpcmd and @content conversion to VARCHAR(8000) as I remember where I got the error. It is whenever the @bcpcmd is VARCHAR(MAX). It works with @content that way, but it is ultimately cutting it off to limit of @bcpcmd.

Good luck!

Kevin
0
 
ModSpaceAuthor Commented:
I would like to open this case again , i need add some more logic to the tsql..

I need to go down further level  and process all the "flowtype" in a given french language..for a given contractid and for a given invoice(main logic)

Can i have the script to process it...

file is ..........<Root>
  <TOInvoiceExtractBatch DocGenRunId="736" RunDt="9/8/2011 5:31:43 PM">
    <DocGenRunId>736</DocGenRunId>
    <RunDt>2011-09-08T05:31:43</RunDt>
    <BatchInvoiceCount>4</BatchInvoiceCount>
    <BatchTotalAmt>3292.64</BatchTotalAmt>
    <InvoiceHeaders List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractHeader">
      <TOInvoiceExtractHeader InvoiceNo="500000634" InvoiceId="636">
        <InvoiceNo>500000634</InvoiceNo>
        <InvoiceId>636</InvoiceId>
        <InvoiceRunDate>2011-09-08T12:00:00</InvoiceRunDate>
        <InvoiceStartDate>2011-07-01T12:00:00</InvoiceStartDate>
        <InvoiceEndDate>2011-08-01T12:00:00</InvoiceEndDate>
        <InvoiceBillingPeriod>7/1/2011 - 8/1/2011</InvoiceBillingPeriod>
        <BusUnitName>xxxxxxxxxxxxxxx</BusUnitName>
        <BusUnitCode>MSC</BusUnitCode>
        <BusUnitId>4</BusUnitId>
        <BusUnitInvoiceName>xxxxxxxxxxxxxxx</BusUnitInvoiceName>
        <BusUnitTradeAs />
        <BusUnitRef>103</BusUnitRef>
        <BusUnitBusRef />
        <BusUnitAltRef />
        <BusUnitTaxCode />
        <BusUnitAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>xxxxxxxxxxxxxxx Swedesford Road</Street>
          <Suburb />
          <City>Berwyn</City>
          <StateProvince>PA</StateProvince>
          <County>Chester</County>
          <ZipCode>19312</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>390296350</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>xxxxxxxxxxxxxxx Swedesford Road,
Berwyn,
Chester,
PA,
19312,
United States</AddressAsFormattedString>
        </BusUnitAddress>
        <BusUnitBranch>None</BusUnitBranch>
        <PartyInvoiceName>Desert Sky Middle School</PartyInvoiceName>
        <PartyBusEmail />
        <PartyHomeEmail />
        <PartyNo>500035</PartyNo>
        <PartyId>89541</PartyId>
        <PartyAccountNo>500035-0001</PartyAccountNo>
        <PartyRef>001Q000000OwssPIAR</PartyRef>
        <PartyBusRef />
        <PartyAltRef />
        <PartyTaxCode />
        <PartyIsElectronicInvoice>false</PartyIsElectronicInvoice>
        <PartyIsNew>false</PartyIsNew>
        <PartyLanguage>French</PartyLanguage>
        <PartyCurrentAccountBalance>0.00</PartyCurrentAccountBalance>
        <PartyPrevInvoiceTotal>0.00</PartyPrevInvoiceTotal>
        <PartyBranch>None</PartyBranch>
        <PartyContactName>George Wallace</PartyContactName>
        <InvoiceDetailCount>1</InvoiceDetailCount>
        <InvoiceTotalAmount>18.00</InvoiceTotalAmount>
        <InvoiceTotalAmtTax>1.64</InvoiceTotalAmtTax>
        <InvoiceTotalGrossAmount>19.64</InvoiceTotalGrossAmount>
        <IsConsolidated>false</IsConsolidated>
        <ConsolidateId>0</ConsolidateId>
        <ConsolidationName>None</ConsolidationName>
        <InvoiceContext>Periodic</InvoiceContext>
        <BillingAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street />
          <Suburb />
          <City />
          <StateProvince />
          <County />
          <ZipCode />
          <CountryRegion />
          <TaxArea />
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString />
        </BillingAddress>
        <LocationAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>98950 Rankin Loop</Street>
          <Suburb />
          <City>Tucson</City>
          <StateProvince>AZ</StateProvince>
          <County />
          <ZipCode>85754</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>030190310</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>98950 Rankin Loop,
Tucson,
AZ,
85754,
United States</AddressAsFormattedString>
        </LocationAddress>
        <InvoiceDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetail">
          <TOInvoiceExtractDetail ContractId="501018" FlowId="294063" AssetHdrId="136021">
            <FlowId>294063</FlowId>
            <AssetHdrId>136021</AssetHdrId>
            <ContractId>501018</ContractId>
            <FlowType>Delivery-Fuel Surcharge</FlowType>
0
 
Kevin CrossChief Technology OfficerCommented:
Not sure what you mean. Is this relating to saving the file OR is this a question on XML manipulation/transformation? If so, you may be better served posting a new question with exact file input you are using and what you want done.
0
 
ModSpaceAuthor Commented:
THis is to the PAQ' tsql to process xml file but i need to go further down InvoiceDetails List and repalce <FlowType> tag  with database value for a given contractid under invocie #.
0
 
Kevin CrossChief Technology OfficerCommented:
Okay, then you want to ask a related question from that thread (I have included the link for your convenience).
0
 
ModSpaceAuthor Commented:
I posted the question..please take a look .thanks
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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