FOR XML EXPLICIT question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML EXPLICIT question

Author

Topic

BravehearT1326
Starting Member

24 Posts

Posted - 2010-09-14 : 06:34:12

Hi there.

I need to produce output in a specific XML format. The normal FOR XML AUTO, RAW etc don't produce the output I need and would appreciate some guidance on using the FOR XML EXPLICIT option.

The format of the output is as follows:

<CurrencyRateHistoricInsert> -- This is a section header
<BaseCurrency> xxx <BaseCurrency> -- This is a value in a table and only a single entry
<CurrencyRatesInstanceList> --Sub-Section Header
<CurrencyRatesInstance> --Sub-Sub-Section Header
<Date> xxxxxxyyyyyy <Date> --value in table
<CurrencyList> --Sub Section Header
<CurrencyRate> --Sub-Sub-Section Header
<Name> xxx <Name> -- Value in table
<Rate> yyy >Rate> -- Value in table
...
...
...

I can get values returned into sections however I cant seem to be able to place section headers....What am I doing wrong?

SELECT 
      1 as TAG,
      NULL as Parent,
      NULL as [CurrencyRateHistoricInsert!1],
      'GBP' as [CurrencyRateHistoricInsert!1!BaseCurrency!element],
      2 as [CurrencyRateHistoricInsert!1!CurrencyRatesInstanceList!ELEMENT],
      NULL as [CurrencyList!2!Date!ELEMENT],
      NULL as [CurrencyList!2!Name!ELEMENT],
      NULL as [CurrencyList!2!Rate!ELEMENT]
from exchange_Rate_History
where effective_date >'20100912'

UNION ALL

SELECT
       2 as TAG,
       1 as Parent,
       Null,
       Null,
       NULL,
       effective_date,
       Currency_Key,
       exchange_rate
from exchange_Rate_History
where effective_date >'20100912'

for XML explicit,  ROOT('MYXML')

I'm getting the following outpur received....

<MYXML>
<CurrencyRateHistoricInsert>
<BaseCurrency>GBP</BaseCurrency>
<CurrencyRatesInstanceList>2</CurrencyRatesInstanceList>
</CurrencyRateHistoricInsert>
<CurrencyRateHistoricInsert>
<BaseCurrency>GBP</BaseCurrency>
<CurrencyRatesInstanceList>2</CurrencyRatesInstanceList>.......
..
..
<CurrencyList>
<Date>2010-09-13T00:00:00</Date>
<Name>ZAR</Name>
<Rate>8.787327</Rate>
</CurrencyList>
<CurrencyList>
<Date>2010-09-13T00:00:00</Date>
<Name>ZAR</Name>
<Rate>10.493266</Rate>
</CurrencyList>
<CurrencyList>
<Date>2010-09-13T00:00:00</Date>
<Name>ZMK</Name>
<Rate>704.780103</Rate>
</CurrencyList>

So its generating the outoput but its more of a formatting issue I think.

BravehearT1326
Starting Member

24 Posts

Posted - 2010-09-14 : 06:42:29

If I amend my query to run the as the following:

SELECT 
      1 as TAG,
      NULL as Parent,
      NULL as [CurrencyRateHistoricInsert!1],
      'GBP' as [CurrencyRateHistoricInsert!1!BaseCurrency!element],
      2 as [CurrencyRateHistoricInsert!1!CurrencyRatesInstanceList!ELEMENT],
      Effective_Date as [CurrencyRateHistoricInsert!1!Date!ELEMENT],
      Currency_Key as [CurrencyRateHistoricInsert!1!Name!ELEMENT],
      Exchange_Rate as [CurrencyRateHistoricInsert!1!Rate!ELEMENT]
from exchange_Rate_History
where effective_date >'20100912'

for XML explicit,  ROOT('MYXML')

It returnd the output:
MYXML>
<CurrencyRateHistoricInsert>
<BaseCurrency>GBP</BaseCurrency>
<CurrencyRatesInstanceList>2</CurrencyRatesInstanceList>
<Date>2010-09-13T00:00:00</Date>
<Name>AE1</Name>
<Rate>0.538591</Rate>
</CurrencyRateHistoricInsert>
<CurrencyRateHistoricInsert>
<BaseCurrency>GBP</BaseCurrency>
<CurrencyRatesInstanceList>2</CurrencyRatesInstanceList>
<Date>2010-09-13T00:00:00</Date>
<Name>AE1</Name>
<Rate>3.421600</Rate>..
..
..

So is it possible to add in attribure headers into the XML output generated?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-14 : 15:44:58

Provide table structure, with sample data, and a clear output you expect.

It is likely people are not sure what you want, or too much for them to create sample data....

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-14 : 17:37:34

If you're using SQL Server 2005, you should consider FOR XML PATH instead, it's a lot easier and neater:

http://www.texastoo.com/post/2010/09/06/FOR-XML-PATH-with-TYPE-is-much-better-than-XML-EXPLICIT.aspx

BravehearT1326
Starting Member

24 Posts

Posted - 2010-09-15 : 09:52:16

I cant use the FOR XML PATH option as it does not provide me with the full layout required by the 3rd party app the XML is going to be used on...... Hence me looking into the FOR XML EXPLICIT option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources