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