Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I need to create an XML file populated by fields data(eg: SQL Server DB table) Expand / Collapse
Author
Message
Posted Tuesday, May 14, 2013 5:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
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.


  Post Attachments 
XML file format.txt (2 views, 2.94 KB)
Post #1452512
Posted Tuesday, May 14, 2013 6:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 12,906, Visits: 31,984
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1452525
Posted Tuesday, May 14, 2013 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
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
Post #1452623
Posted Tuesday, May 14, 2013 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
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]
Post #1452628
Posted Tuesday, May 14, 2013 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
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


  Post Attachments 
XML file format.txt (20 views, 2.94 KB)
Post #1452637
Posted Tuesday, May 14, 2013 1:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
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


  Post Attachments 
fsa_qry.txt (3 views, 4.02 KB)
Post #1452785
Posted Wednesday, May 15, 2013 2:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
Hi Lowell

Can you help me on the query please.

Many thanks
V
Post #1452982
Posted Friday, May 17, 2013 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 3:45 AM
Points: 9, Visits: 35
Firiends any one has worked on creating a SQL to generate this kind of XML, Help me please.
Post #1453910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse