how to feed xml file with data from SQL Server?

Question

I need to feed periodically specific format xml with data which I can get from SQL Server 2012. What is the proper or more efficient way of doing this to automate the task as much as possible? I can select from SQL Database all data (values for the tags) but is it possible to save them in specific XML format and generate the whole file from the query? Or rather I need to inject the data into that external file. The xml file for one client looks like this:

<?xml version="1.0"?>
<AddCases>
<AddCase>
<Case>
<Debtor>
<Consumer>
<FirstName>John</FirstName>
<Surname>Doe</Surname>
<Citizenship>Mars</Citizenship>
<Birthday>1979-08-31</Birthday>
<SSN>79083102949</SSN>
<Address>
<Street>135 Main St</Street>
<City>Linden</City>
<ZipCode>5556</ZipCode>
<Country>US</Country>
</Address>
<Sex>men</Sex>
</Consumer>
</Debtor>

<History>
<InformationDate>2014-01-02</InformationDate>
<PaymentStatusCode>00</PaymentStatusCode>
<OverDue>0</OverDue>
<OverdueAmount>0</OverdueAmount>
<DueAmount>42000</DueAmount>
</History>
.
.
.
<SourceSymbol>pap</SourceSymbol>
</Consent>
</Consents>
</CreditAccount>
</CreditAccouts>
</Case>
</AddCase>

I recommend an application written in ColdFusion, .net, or something similar that would query the db and write the xml file. Then schedule that application to run at the desired frequency. — Dan Bracuk
– Dan Bracuk, Commented Aug 7, 2014 at 23:23

Community · Accepted Answer · 2017-05-23 10:26:17Z

1

Have a look at FOR XML. It gives quite a few options and returns XML so you . I've done a lot of explict explicit mode queries where you use unions to query each layer. A little ugly but it works.

select 1 as tag,
null as parent,
'' as [AddCases!1!xmlns],
null as [AddCase!2!xmlns],
null as [Case!3!xmlns],
null as [Debtor!4!FirstName!elementxsinil],
null as [Debtor!4!LastName!elementxsinil]

union 

select 2 as tag,
1 as parent,
null as [AddCases!1!xmlns],
'' as [AddCase!2!xmlns],
null as [Case!3!xmlns],
null as [Debtor!4!FirstName!elementxsinil],
null as [Debtor!4!LastName!elementxsinil]

union 

select 3 as tag,
2 as parent,
null as [AddCases!1!xmlns],
null as [AddCase!2!xmlns],
null as [Case!3!xmlns],
null as [Debtor!4!FirstName!elementxsinil],
null as [Debtor!4!LastName!elementxsinil]

union

select 4 as tag,
3 as parent,
null as [AddCases!1!xmlns],
null as [AddCase!2!xmlns],
null as [Case!3!xmlns],
'Jack' as [Debtor!4!First Name!elementxsinil],
'Smith' as [Debtor!4!Last Name!elementxsinil]
--your from here

for xml explicit

Which generates:

<AddCases xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="">
  <AddCase xmlns="">
    <Case>
      <Debtor>
        <FirstName>Jack</FirstName>
        <LastName>Smith</LastName>
      </Debtor>
    </Case>
  </AddCase>
</AddCases>

As for generating a file look here. I would probably go with the sqlcmd option and run it using the Windows task scheduler to automate it.

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Aug 8, 2014 at 0:36

drewk

1162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

Using FOR XML PATH might be another and usually much easier / less convoluted solution

Zulu Z Over a year ago

thanks, I was hoping something cleaner but that would do as well maybe.

drewk Over a year ago

The AUTO and PATH are much cleaner if they work for you. They didn't for me so I've had to use EXPLICIT.

Collectives™ on Stack Overflow

how to feed xml file with data from SQL Server?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related