Generate XML file base on XSD from sql server table

Question

Generate XML file base on XSD from sql server table

latitiacasta

SSCommitted

Points: 1982
More actions
September 13, 2016 at 12:31 am

#104330

Hi All,
I have requirement to generate xml from csv file. I had load csv file data into sql server and create one temporary table which has 15 M rows. Base on Policy number need to generate xml file in require format. I am attaching sql script to create xml test table and insert one row. Also attaching xsd file for xml format. I had tried for xml but it provide result in plain xml file. Is there any 3rd party tool which can generate xml base on xsd.
Appreciate your kind input.
Thanks,
Lat

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Whether you use a 3rd party tool or not the challenge is the same, the columns must be manually mapped to elements within the XML as the element names differ from the column names.

😎

Creating the XML in SQL Server is straight forward and I wouldn't even think of using external tools for this task, simply open the XSD in Visual Studio's Schema Explorer and start typing;-)

Here is a quick example which should be enough to get you started.

SELECT

XT.City AS 'address-lookup/address/city'

,XT.Country AS 'address-lookup/address/country'

,XT.Message AS 'messages/message/body'

,XT.Code AS 'messages/message/campaign-code'

FROM dbo.xmltest XT

FOR XML PATH(''), ROOT('data'),TYPE, ELEMENTS XSINIL;

Output

<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<address-lookup>

<address>

<city>TestLondon</city>

<country>TestLondon</country>

</address>

</address-lookup>

<messages>

<message>

<body>TestMessage</body>

<campaign-code>TestCode</campaign-code>

</message>

</messages>

</data>

latitiacasta SSCommitted Points: 1982 More actions · Answer 2

Thanks Eirikur Eiriksson, i will try from my side first if i stuck anywhere will raise it 🙂

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

latitiacasta (9/13/2016)
Thanks Eirikur Eiriksson, i will try from my side first if i stuck anywhere will raise it 🙂

You are very welcome.

😎

If you have any problems just post it on this thread.

latitiacasta SSCommitted Points: 1982 More actions · Answer 4

Hi,

I have two table Account and Address both join base on accountuid. I have created xml using below code but it is not in format which i want. PFA query for creating tables with data. Appreciate your help.

Query:

Select

a.accountuid 'Account/AccountUID',

a.accountid 'Account/AccountID',

(

select ad.city 'Account/address/City',

ad.country 'Account/address/Country'

From address ad where a.accountuid=ad.accountuid

FOR XML PATH(''),TYPE, ELEMENTS XSINIL),

a.creditscore 'Account/Creditscore'

from Account a

FOR XML PATH(''), ROOT('data'),TYPE, ELEMENTS XSINIL

Result:

</Account>

<City>London</City>

</address>

</Account>

<City>Leeds</City>

</address>

</Account>

</Account>

</data>

Require result:

<City>London</City>

</address>

<City>Leeds</City>

</address>

</Account>

</data>

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Almost there, just needed a little adjustment 😉

😎

SELECT

ACC.AccountUID AS 'AccountUID'

,ACC.AccountID AS 'AccountID'

,(SELECT

ADR.City AS 'City'

,ADR.Country AS 'Country'

FROM dbo.Address ADR

WHERE ACC.AccountUID = ADR.AccountUID

FOR XML PATH('address'), TYPE)

,ACC.CreditScore AS 'Creditscore'

FROM dbo.Account ACC

FOR XML PATH('Account'), ROOT('data'),TYPE, ELEMENTS XSINIL;

Output

<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Account>

<AccountUID>b853fe5b-9598-4988-bd65-3c5664db3282</AccountUID>

<AccountID>1</AccountID>

<address>

<City>London</City>

<Country>UK</Country>

</address>

<address>

<City>Leeds</City>

<Country>UK</Country>

</address>

<Creditscore>7.600000000000000e+002</Creditscore>

</Account>

</data>

latitiacasta SSCommitted Points: 1982 More actions · Answer 6

Hi,

Thanks for your help. one small doubt, How can i add dummy node. PFA . create table with data script. xmlnode.sql which i am using. provided resultxml vs requireresultxml files. Also need to know how can i merge two xml code.

Previous file for Account and address and now its for Policy.

something like below

<Data>

+ <Account>

+ <Policy>

</Data>

--Lat

latitiacasta SSCommitted Points: 1982 More actions · Answer 7

latitiacasta

SSCommitted

Points: 1982

September 15, 2016 at 4:58 am

#1901065

sorry forgot to add attachment

latitiacasta SSCommitted Points: 1982 More actions · Answer 8

HI,

It's done. I have added tables with auto incremental id and foreign key to create dummy nodes. Once again Thanks for your help 😛

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 9

latitiacasta (9/16/2016)
HI,
It's done. I have added tables with auto incremental id and foreign key to create dummy nodes. Once again Thanks for your help 😛

Good job and thanks for the feedback

😎