August 11, 2010 at 2:14 am
Hi there - looking for guidance on generating output in XML format. I have a new application that we need to drip feed info into and it needs to be in a specific XML format. Having not done any of this in the past and my TSQL being not as good as it should I'm looking for guidance on how best to do this.
I have managed to generate part of the XML file structure successfully however there appears to be around 3-4 different sections in it and looking to see what is the best way for this structure to be generated. I dont know how to join all the sections together
The XML file needs to be in the structure below. The section in BOLD is what I have managed to generate so far using the FOR XML PATH , ROOT options but I need to be able to generate the totals section details at the top. Whats the best way to do this and produce 1 large XML structured file? Do i need to build temp tables - insert the data into them then extract.....if so how would I build the different sections?
Any help appreciated.
--<counters>
<CurrencyTotalCount>13</CurrencyTotalCount>
<CurrencyRateInsertCount>1</CurrencyRateInsertCount>
<CurrencyRateHistoricInsertCount>12</CurrencyRateHistoricInsertCount>
<PersonTotalCount>0</PersonTotalCount>
<PersonInsertCount>0</PersonInsertCount>
<PersonUpdateCount>0</PersonUpdateCount>
<PolicyTotalCount>0</PolicyTotalCount>
<PolicyInsertCount>0</PolicyInsertCount>
<PolicyRefreshCount>0</PolicyRefreshCount>
<PolicyRenewalCount>0</PolicyRenewalCount>
<PolicyDeleteCount>0</PolicyDeleteCount>
<PolicyUpdateCount>0</PolicyUpdateCount>
<PolicyCancelCount>0</PolicyCancelCount>
<PolicyReinstateCount>0</PolicyReinstateCount>
</counters>
- <CurrencyRateInsert>
<BaseCurrency>GBP</BaseCurrency>
- <CurrencyRatesInstance>
<Date>2008-07-02T00:00:00</Date>
- <CurrencyList>
- <CurrencyRate>
<Name>ZZB</Name>
<Rate>1</Rate>
</CurrencyRate>
</CurrencyList>
</CurrencyRatesInstance>
</CurrencyRateInsert>
- <CurrencyRateHistoricInsert>
<BaseCurrency>GBP</BaseCurrency>
- <CurrencyRatesInstanceList>
- <CurrencyRatesInstance>
<Date>2005-01-01T00:00:00</Date>
- <CurrencyList>
- <CurrencyRate>
<Name>ZZA</Name>
<Rate>1.2661292721</Rate>
</CurrencyRate>
- <CurrencyRate>
<Name>ZZB</Name>
<Rate>1</Rate>
</CurrencyRate>
</CurrencyList>
</CurrencyRatesInstance>
- <CurrencyRatesInstance>
<Date>2005-04-01T00:00:00</Date>
- <CurrencyList>
- <CurrencyRate>
<Name>ZZA</Name>
<Rate>1.2661292721</Rate>
</CurrencyRate>
- <CurrencyRate>
<Name>ZZB</Name>
<Rate>1</Rate>
</CurrencyRate>
</CurrencyList>
</CurrencyRatesInstance>
August 11, 2010 at 5:04 am
Please provide table def and sample data so we have something to test against.
Also, please show us what you've tried so far.
August 11, 2010 at 6:56 am
Here's the code I've ran so far...I'll post table defs and sample data shortly...
DROP TABLE #CurrencyList
go
DROP Table #Effective_Date
go
CREATE TABLE #CurrencyList(CurrencyList_Date datetime NOT NULL)
SELECT DISTINCT
Effective_Date
INTO#Effective_Date
FROMdbo.Exchange_Rate_History
SELECT
CurrencyRatesInstance.Effective_dateAS "CurrencyRatesInstance/Date",
CurrencyList.CurrencyList_Date AS "CurrencyRatesInstance/CurrencyList",
CurrencyRate.Currency_KeyAS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Name",
CurrencyRate.Exchange_RateAS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Rate"
FROM
#Effective_Date CurrencyRatesInstance
INNER JOIN
Exchange_Rate_History_View CurrencyRate
ON
CurrencyRatesInstance.effective_Date = CurrencyRate.effective_Date
LEFT OUTER JOIN
#CurrencyList CurrencyList
ON
CurrencyList.CurrencyList_Date = CurrencyRate.effective_Date
WHEREYEAR(CurrencyRatesInstance.effective_Date) = (2010)
ANDMONTH(CurrencyRatesInstance.effective_Date) = (2)
andDAY(CurrencyRatesInstance.effective_Date) = (1)
ANDEnvironment_Key = 'DL'
ORDER BY
CurrencyRatesInstance.effective_Date DESC
FOR XML PATH ('CurrencyRatesInstanceList'), ROOT ('CurrencyRateHistoricInsert')
August 11, 2010 at 8:18 am
Here's some code to build tables and sample data. This generates the aforementioned results. I then need to be able to add other XML fields as per my original post and I dont know how to do this? Do I use joins???
--- create table
USE [<database name here>]
GO
/****** Object: Table [dbo].[Exchange_Rate_History] Script Date: 08/11/2010 14:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Exchange_Rate_History](
[Currency_Key] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[Effective_Date] [datetime] NOT NULL,
[Exchange_Rate] [numeric](13, 6) NOT NULL,
[Environment_Key] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Exchange_Rate_History] PRIMARY KEY CLUSTERED
(
[Currency_Key] ASC,
[Effective_Date] ASC,
[Environment_Key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- Create View
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEVIEW [dbo].[Exchange_Rate_History_View]
AS
SELECT *
FROM dbo.Exchange_Rate_History
-- Insert some test values
INSERT INTO [<database name here>].[dbo].[Exchange_Rate_History]
([Currency_Key]
,[Effective_Date]
,[Exchange_Rate]
,[Environment_Key])
VALUES
('ZZA','2005-01-01T00:00:00','1','DL')
go
INSERT INTO [<database name here>].[dbo].[Exchange_Rate_History]
([Currency_Key]
,[Effective_Date]
,[Exchange_Rate]
,[Environment_Key])
VALUES
('ZZB','2005-01-01T00:00:00','1','DL')
go
INSERT INTO [<database name here>].[dbo].[Exchange_Rate_History]
([Currency_Key]
,[Effective_Date]
,[Exchange_Rate]
,[Environment_Key])
VALUES
('GBP','2005-01-01T00:00:00','1.123456','UK')
August 11, 2010 at 12:40 pm
Here's how I'd do it (looks a little messy though, maybe someone else will come up with a better solution..):
SELECT
CAST (
(SELECT
COUNT(*) AS CurrencyTotalCount,
1 AS CurrencyRateInsertCount,
12 AS CurrencyRateHistoricInsertCount,
0 AS PersonTotalCount,
0 AS PersonInsertCount,
0 AS PersonUpdateCount,
0 AS PolicyTotalCount
FROM #Effective_Date
FOR XML PATH('')
) AS XML) counters,
CAST(
( SELECT
CurrencyRatesInstance.Effective_date AS "CurrencyRatesInstance/DATE",
CurrencyList.CurrencyList_Date AS "CurrencyRatesInstance/CurrencyList",
CurrencyRate.Currency_Key AS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Name",
CurrencyRate.Exchange_Rate AS "CurrencyRatesInstance/CurrencyList/CurrencyRate/Rate"
FROM #Effective_Date CurrencyRatesInstance
INNER JOIN Exchange_Rate_History_View CurrencyRate
ON CurrencyRatesInstance.effective_Date = CurrencyRate.effective_Date
LEFT OUTER JOIN #CurrencyList CurrencyList
ON CurrencyList.CurrencyList_Date = CurrencyRate.effective_Date
WHERE Environment_Key = 'DL'
ORDER BY CurrencyRatesInstance.effective_Date DESC
FOR XML PATH ('CurrencyRateHistoricInsert')
) AS XML) CurrencyRatesInstanceList
FOR XML PATH ('')
August 11, 2010 at 4:13 pm
A couple of things. In order for us to help you with your problem, the sample data has to be representative of your live data. Since it's obvious that you want some kind of historical counts, having only one entry per currency isn't sufficient to have a representative sample.
Also, your XML is improperly formed, because the <CurrencyRateHistoricInsert> has no ending tag, so I can't even tell from your XML what belongs under that element.
You don't define what differentiates a "Insert" from a "HistoricInsert". I'm guessing that the most recent for a given currency would be the Insert and everything else for that currency would be a HistoricInsert, but it would be best if you mentioned it from the start.
I don't see how your BaseCurrency is determined from you data? Is that a flag or a foreign key or something else? That should also be included in your sample data.
I wanted to see if I could find a better solution than Lutz, but there were just too many questions.
I did notice something in your sample SQL code. You create a temporary table by selecting DISTINCT values from a datetime field and then you join that temporary table to the original table on the original field. We know that the value from the temporary table is equal to the value from the original field, because that's part of the join condition. We know that the join is not going to filter out any non-null values, because the original table was the source of the temporary table. You would get the exact same results by simply using the original field from the original table.
A suggestion in your one WHERE clause. You have three separate tests on the same field, when you could accomplish the same thing much more efficiently with one test. The following
WHERE YEAR(CurrencyRatesInstance.effective_Date) = (2010)
AND MONTH(CurrencyRatesInstance.effective_Date) = (2)
and DAY(CurrencyRatesInstance.effective_Date) = (1)
can be rewritten as
WHERE DateDiff(Day, CurrencyRatesInstance, '2010-02-01T00:00:00') = 0
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2010 at 7:13 pm
I'm not sure if this is relevant to this question, but one note I will make is that to insert a repeating XML structure inside another XML Structure you must use a sub-select with the FOR XML Path ,myrepeatingnode>, TYPE
So your query becomes something like
SELECT
col1 AS Col1,
Id AS Id,
(SELECT
ColA AS ColA
FROM TableA
WHERE TableA.ParentId = Table1.Id
FOR XML
PATH ('RepeatNode'),
TYPE)
FROM Table1
FOR XML
PATH ('MyData'),
ROOT ('root')
which should produce an XML document with a structure similar to
<root>
<MyData>
<Col1></Col1>
<Id></Id>
<RepeatNode>
<ColA></ColA>
</RepeatNode>
<RepeatNode>
<ColA></ColA>
</RepeatNode>
</MyData>
<MyData>
<Col1></Col1>
<Id></Id>
<RepeatNode>
<ColA></ColA>
</RepeatNode>
<RepeatNode>
<ColA></ColA>
</RepeatNode>
</MyData>
</root>
August 12, 2010 at 3:02 am
Folks - many thanks for taking the time to reply and comment accordingly. As I mentioned previously I have not done any of this in the past so apologies if my initial coding and sample data is incorrect from what you normally expect but please be assured it was not intentional.
All of your replies have given me some ideas where I need to go now to try and get this output generated and the ways of writing the TSQL query to generate it. It looks like i am on a long TSQL journey but now that i have started i intend to arrive at the destination.
Thanks again.
Ronnie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply