I need to create an XML file populated by fields data(eg: SQL Server DB table)

Question

I need to create an XML file populated by fields data(eg: SQL Server DB table)

vsmeruga

SSC Veteran

Points: 291
More actions
May 14, 2013 at 5:29 am

#277724

I have the following XMLSchema template:
<?xml version="1.0" encoding="UTF-8"?>
<DataMarketsFeed xmlns="http://www.test.co.uk/XMLSchema/DataMarketsFeed-v1-2" xmlns:cmn="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataFeedHeader xmlns="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2">
<FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion>
<Submitter>
<SubmittingFirm>123456</SubmittingFirm>
</Submitter>
<ReportDetails>
<ReportCreationDate>2009-03-12</ReportCreationDate>
<ReportIdentifier>March09</ReportIdentifier>
</ReportDetails>
</DataFeedHeader>
<DataMarketsFeedMsg>
<CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2">
<FirmDataRef>123789</FirmDataRef>
<TransRef>123789</TransRef>
<Cancellation>false</Cancellation>
<Status>N</Status>
</CoreItemsMkt>
<Transaction>
<ReportingFirmId>
<ReportingFirmCode type="R">123456</ReportingFirmCode>
</ReportingFirmId>
<TradingDate>2009-02-27</TradingDate>
<TradingTime>08:02:00</TradingTime>
<BuySell>B</BuySell>
<TradingCapacity>P</TradingCapacity>
<InstrumentIdentification>
<InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode>
</InstrumentIdentification>
<MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate>
<TypeOfDerivative>F</TypeOfDerivative>
<PriceMultiplier>10</PriceMultiplier>
<UnitPrice>3836</UnitPrice>
<PriceNotation>GBP</PriceNotation>
<Quantity>3.00</Quantity>
<CounterpartyOne>
<CounterpartyCode type="I">QWERTYU</CounterpartyCode>
</CounterpartyOne>
<CounterpartyTwo>
<CounterpartyCode type="I">ASB00037</CounterpartyCode>
</CounterpartyTwo>
<VenueIdentificationTypeCode>M</VenueIdentificationTypeCode>
<UnitPriceTypeCode>C</UnitPriceTypeCode>
</Transaction>
</DataMarketsFeedMsg>
</DataMarketsFeed>
<CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2">
<FirmDataRef>123789</FirmDataRef>
<TransRef>123789</TransRef>
<Cancellation>false</Cancellation>
<Status>N</Status>
</CoreItemsMkt>
<Transaction>
<ReportingFirmId>
<ReportingFirmCode type="R">123456</ReportingFirmCode>
</ReportingFirmId>
<TradingDate>2009-02-27</TradingDate>
<TradingTime>08:02:00</TradingTime>
<BuySell>B</BuySell>
<TradingCapacity>P</TradingCapacity>
<InstrumentIdentification>
<InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode>
</InstrumentIdentification>
<MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate>
<TypeOfDerivative>F</TypeOfDerivative>
<PriceMultiplier>10</PriceMultiplier>
<UnitPrice>3836</UnitPrice>
<PriceNotation>GBP</PriceNotation>
<Quantity>3.00</Quantity>
<CounterpartyOne>
<CounterpartyCode type="I">QWERTYU</CounterpartyCode>
</CounterpartyOne>
<CounterpartyTwo>
<CounterpartyCode type="I">ASB00037</CounterpartyCode>
</CounterpartyTwo>
<VenueIdentificationTypeCode>M</VenueIdentificationTypeCode>
<UnitPriceTypeCode>C</UnitPriceTypeCode>
</Transaction>
</DataMarketsFeedMsg>
</DataMarketsFeed>
Can some one help me with a SQL Server 2008 - SQL Query to generate the above format ?. Attached the XML output file format as well.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, the xml you posted is malformed; it is either missing two tags in the middle, or these two tags below need to be removed from the middle:

</DataMarketsFeedMsg>
</DataMarketsFeed>

the real question is, do you already have a suite of tables in SQL already in place that support the fields that are required in this xml?

without that, you are stuck; it's too much guesswork for us to create the DDL to support it.

What code do you have so far?

what have you tried so far?

can you post the DDL of the tables, and a sample query that pulls the fields out (which we could help convert to xml)

FYI this is valid xml, where i removed the extra tags from the middle; is htis right?

<?xml version="1.0" encoding="UTF-8"?>

<DataMarketsFeed xmlns="http://www.test.co.uk/XMLSchema/DataMarketsFeed-v1-2" xmlns:cmn="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<DataFeedHeader xmlns="http://www.test.co.uk/XMLSchema/DataFeedCommon-v1-2">

<FeedTargetSchemaVersion>1.2</FeedTargetSchemaVersion>

<Submitter>

<SubmittingFirm>123456</SubmittingFirm>

</Submitter>

<ReportDetails>

<ReportCreationDate>2009-03-12</ReportCreationDate>

<ReportIdentifier>March09</ReportIdentifier>

</ReportDetails>

</DataFeedHeader>

<DataMarketsFeedMsg>

<CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2">

<FirmDataRef>123789</FirmDataRef>

<TransRef>123789</TransRef>

<Cancellation>false</Cancellation>

<Status>N</Status>

</CoreItemsMkt>

<Transaction>

<ReportingFirmId>

<ReportingFirmCode type="R">123456</ReportingFirmCode>

</ReportingFirmId>

<TradingDate>2009-02-27</TradingDate>

<TradingTime>08:02:00</TradingTime>

<BuySell>B</BuySell>

<TradingCapacity>P</TradingCapacity>

<InstrumentIdentification>

<InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode>

</InstrumentIdentification>

<MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate>

<TypeOfDerivative>F</TypeOfDerivative>

<PriceMultiplier>10</PriceMultiplier>

<UnitPrice>3836</UnitPrice>

<PriceNotation>GBP</PriceNotation>

<Quantity>3.00</Quantity>

<CounterpartyOne>

<CounterpartyCode type="I">QWERTYU</CounterpartyCode>

</CounterpartyOne>

<CounterpartyTwo>

<CounterpartyCode type="I">ASB00037</CounterpartyCode>

</CounterpartyTwo>

<VenueIdentificationTypeCode>M</VenueIdentificationTypeCode>

<UnitPriceTypeCode>C</UnitPriceTypeCode>

</Transaction>

<CoreItemsMkt xmlns="http://www.Test.co.uk/XMLSchema/DataMarketsFeed-v1-2">

<FirmDataRef>123789</FirmDataRef>

<TransRef>123789</TransRef>

<Cancellation>false</Cancellation>

<Status>N</Status>

</CoreItemsMkt>

<Transaction>

<ReportingFirmId>

<ReportingFirmCode type="R">123456</ReportingFirmCode>

</ReportingFirmId>

<TradingDate>2009-02-27</TradingDate>

<TradingTime>08:02:00</TradingTime>

<BuySell>B</BuySell>

<TradingCapacity>P</TradingCapacity>

<InstrumentIdentification>

<InstrumentIdentificationCode type="A">R</InstrumentIdentificationCode>

</InstrumentIdentification>

<MaturityExerciseDeliveryDate>2009-03-31</MaturityExerciseDeliveryDate>

<TypeOfDerivative>F</TypeOfDerivative>

<PriceMultiplier>10</PriceMultiplier>

<UnitPrice>3836</UnitPrice>

<PriceNotation>GBP</PriceNotation>

<Quantity>3.00</Quantity>

<CounterpartyOne>

<CounterpartyCode type="I">QWERTYU</CounterpartyCode>

</CounterpartyOne>

<CounterpartyTwo>

<CounterpartyCode type="I">ASB00037</CounterpartyCode>

</CounterpartyTwo>

<VenueIdentificationTypeCode>M</VenueIdentificationTypeCode>

<UnitPriceTypeCode>C</UnitPriceTypeCode>

</Transaction>

</DataMarketsFeedMsg>

</DataMarketsFeed>

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vsmeruga SSC Veteran Points: 291 More actions · Answer 2

Hi

Here it is Table structure.

CREATE TABLE [dbo].[FSA_REPORT_DATA](

[Unique_Seq_Ref_number] [int] IDENTITY(1,1) NOT NULL,

[Record_Type_Flag] [int] NULL,

[Reporting_Firm_Identification] [nvarchar](50) NULL,

[Reporting_Firm_Code_Type] [nvarchar](1) NULL,

[Trade_Date] [nvarchar](10) NULL,

[Trade_Time] [nvarchar](8) NULL,

[Buy_Sell_Indicator] [nvarchar](1) NULL,

[Trading_Capacity] [nvarchar](1) NULL,

[Instrument_identification] [nvarchar](50) NULL,

[Instrument_Identifier_Type] [nvarchar](1) NULL,

[Instrument_Description] [nvarchar](255) NULL,

[Underlying_Instrument_Identification] [nvarchar](50) NULL,

[Instrument_Type] [nvarchar](1) NULL,

[Maturity_exercise_delivery_date] [nvarchar](10) NULL,

[Derivative_type] [nvarchar](1) NULL,

[Put_call_Identifier] [nvarchar](1) NULL,

[Strike_price] [float] NULL,

[Price_multiplier] [float] NULL,

[Unit_price] [float] NULL,

[Price_notation] [nvarchar](3) NULL,

[Quantity] [float] NULL,

[counterparty_code] [nvarchar](50) NULL,

[Counterparty_code_type] [nvarchar](1) NULL,

[Venue_Identification] [nvarchar](4) NULL,

[Transaction_reference_number] [nvarchar](50) NULL,

[Report_status] [nvarchar](1) NULL,

[Client_Counterparty_2_code] [nvarchar](50) NULL,

[Client_code_type] [nvarchar](1) NULL,

[Venue_Identification_code_type] [nvarchar](1) NULL,

[Unit_price_type_code] [nvarchar](1) NULL,

[Reserved] [nvarchar](50) NULL,

[End_of_Record] [nvarchar](50) NULL,

[FileName] [nvarchar](500) NULL,

[Transaction_status] [nvarchar](500) NULL,

PRIMARY KEY CLUSTERED

(

[Unique_Seq_Ref_number] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

I can give data( 3 records) for this in the evening.

-- Working Query:

select

(select

(

SELECT

'1.2' AS 'FeedTargetSchemaVersion'

FOR XML PATH(''),TYPE

)

,(

select distinct Reporting_Firm_Identification as "Submitter/SubmittingFirm",

substring(convert(varchar,getdate(),120),1,10) as "ReportDetails/ReportCreationDate",

'000GMI' as "ReportDetails/ReportIdentifier"

from dbo.FSA_REPORT_DATA_x where Reporting_Firm_Identification = 114227

FOR XML PATH (''),type

)

FOR XML PATH(''),Root('FSAFeedHeader'),type),

(

SELECT(select ( select ('')fOR XML PATH(''), Root('FSAMarketsFeed'),ELEMENTS XSINIL)FOR XML PATH(''), Root('CoreItemsMkt'),type),

FOR XML PATH(''),Root('FSAMarketsFeedMsg'),type)

FOR XML PATH(''), Root('FSAMarketsFeed'),ELEMENTS XSINIL

vsmeruga SSC Veteran Points: 291 More actions · Answer 3

Select Query for the above DDL

SELECT [Unique_Seq_Ref_number]

,[Record_Type_Flag]

,[Reporting_Firm_Identification]

,[Reporting_Firm_Code_Type]

,[Trade_Date]

,[Trade_Time]

,[Buy_Sell_Indicator]

,[Trading_Capacity]

,[Instrument_identification]

,[Instrument_Identifier_Type]

,[Instrument_Description]

,[Underlying_Instrument_Identification]

,[Instrument_Type]

,[Maturity_exercise_delivery_date]

,[Derivative_type]

,[Put_call_Identifier]

,[Strike_price]

,[Price_multiplier]

,[Unit_price]

,[Price_notation]

,[Quantity]

,[counterparty_code]

,[Counterparty_code_type]

,[Venue_Identification]

,[Transaction_reference_number]

,[Report_status]

,[Client_Counterparty_2_code]

,[Client_code_type]

,[Venue_Identification_code_type]

,[Unit_price_type_code]

,[Reserved]

,[End_of_Record]

,[FileName]

,[Transaction_status]

FROM [FSAReportDB].[dbo].[FSA_REPORT_DATA]

vsmeruga SSC Veteran Points: 291 More actions · Answer 4

Hi

Attached XML output file to be generated one more time. If you say i missed/put some extra tags in my first post. it is corrected now.

Many thanks

VJ

vsmeruga SSC Veteran Points: 291 More actions · Answer 5

Hi

As requested , i have uploaded DDL script to create table and few rows to insert . please help me with SQL query to generate XML.

Many thanks

vsmeruga SSC Veteran Points: 291 More actions · Answer 6

Hi Lowell

Can you help me on the query please.

Many thanks

V

vsmeruga SSC Veteran Points: 291 More actions · Answer 7

Firiends any one has worked on creating a SQL to generate this kind of XML, Help me please.