January 20, 2012 at 4:25 pm
Hi Everyone,
I want to create XML file from sql table without using FOR XML EXPLICIT. Because I tried to doing it but in my case its so difficult.
Below is the sample data and expected result.
CREATE TABLE #table1
(
AccountNo INT
)
INSERT INTO #table1
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
SELECT * FROM #table1 t
CREATE TABLE #detail
(
AccountNo INT,
Color VARCHAR(20),
ProductId INT,
Total INT,
Fees INT
)
INSERT INTO #detail
SELECT 1,'Black',123,100,3
UNION ALL
SELECT 1,'Red',234,110,3
UNION ALL
SELECT 2,'White',675,250,4
UNION ALL
SELECT 2,'Yellow',456,100,3
UNION ALL
SELECT 3,'Black',123,400,4
UNION ALL
SELECT 2,'Black',123,100,3
UNION ALL
SELECT 3,'Pink',354,1000,45
UNION ALL
SELECT 3,'Black',123,100,3
UNION ALL
SELECT 3,'Purple',789,1010,30
UNION ALL
SELECT 4,'Black',567,200,6
UNION ALL
SELECT 4,'White',234,300,7
UNION ALL
SELECT 4,'Red',789,400,5
SELECT * FROM #detail
---------------------------------------
/*
<Account>
<AccountNo>1</AccountNo>
</Account>
<TransactionBasedOnColor>
<StartDate>20110101</StartDate>
<EndDate>20120101</EndDate>
<ColorRed>
<ColorType>
<Color>Red</Color>
</ColorType>
<ProductId>234</ProductId>
<Currency>
<CurrencyType>USD</CurrencyType>
<CurrencyRate>1</CurrencyRate>
</Currency>
<Total>110</Total>
<Fees>3</Fees>
</ColorRed>
<ColorBlack>
<ColorType>
<Color>Black</Color>
</ColorType>
<ProductId>123</ProductId>
<Currency>
<CurrencyType>USD</CurrencyType>
<CurrencyRate>1</CurrencyRate>
</Currency>
<Total>100</Total>
<Fees>3</Fees>
</ColorBlack>
</TransactionBasedOnColor>
<Account>
<AccountNo>2</AccountNo>
</Account>
<TransactionBasedOnColor>
<StartDate>20110101</StartDate>
<EndDate>20120101</EndDate>
<ColorWhite>
<ColorType>
<Color>White</Color>
</ColorType>
<ProductId>675</ProductId>
<Currency>
<CurrencyType>USD</CurrencyType>
<CurrencyRate>1</CurrencyRate>
</Currency>
<Total>250</Total>
<Fees>4</Fees>
</ColorWhite>
<ColorYellow>
<ColorType>
<Color>Yellow</Color>
</ColorType>
<ProductId>456</ProductId>
<Currency>
<CurrencyType>USD</CurrencyType>
<CurrencyRate>1</CurrencyRate>
</Currency>
<Total>100</Total>
<Fees>3</Fees>
</ColorYellow>
</TransactionBasedOnColor>
*/
-------------------------
I tried following , but its hard to cobine result.
SELECT
Color 'ColorType/Color',
ProductId ,
'USD' 'Currency/CurrencyType',
1 'Currency/CurrencyRate',
Total ,
Fees
FROM #detail d WHERE color='Red' AND AccountNo=1
FOR XML PATH('ColorRed')
SELECT
Color 'ColorType/Color',
ProductId ,
'USD' 'Currency/CurrencyType',
1 'Currency/CurrencyRate',
Total ,
Fees
FROM #detail d WHERE color='Black' AND AccountNo=1
FOR XML PATH('ColorBlack')
SELECT
Color 'ColorType/Color',
ProductId ,
'USD' 'Currency/CurrencyType',
1 'Currency/CurrencyRate',
Total ,
Fees
FROM #detail d WHERE color='White' AND AccountNo=2
FOR XML PATH('ColorWhite')
SELECT
Color 'ColorType/Color',
ProductId ,
'USD' 'Currency/CurrencyType',
1 'Currency/CurrencyRate',
Total ,
Fees
FROM #detail d WHERE color='Yellow' AND AccountNo=2
FOR XML PATH('ColorYellow')
January 20, 2012 at 4:53 pm
I don't believe you'll be able to do this without Explicit because you're changing the attribute tags instead of internal values. I'm not an expert on XML via T-SQL, but I didn't want you to end up left hanging till Monday if none of the folks who are a lot better than me can swing through.
You could probably create some kind of dynamic structure that will repeat for all the necessary values for colors by appending the XML to itself from each iteration, and then wrapping your header/trailer tags to the XML, then shipping that to the .xml file you'd like to build. I'm relatively sure that's your only way out of this one.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 21, 2012 at 6:25 am
The following may be a good starting point for the desired results. NOTE: You may want to create portions of this SQL dynamically if there are many different colors to report.
select
a.AccountNo as "Account/AccountNo"
,(select
'20110101' as StartDate
,'20120101' as EndDate
,(select
(select d.Color as "ColorType/Color" ,d.ProductId ,'USD' as "Currency/CurrencyType" ,1 as "Currency/CurrencyRate" ,d.Total ,d.Fees
where d.Color = 'White' for xml path('ColorWhite') ,type
) -- White
,(select d.Color as "ColorType/Color" ,d.ProductId ,'USD' as "Currency/CurrencyType" ,1 as "Currency/CurrencyRate" ,d.Total ,d.Fees
where d.Color = 'Yellow' for xml path('ColorYellow') ,type
) -- Yellow
,(select d.Color as "ColorType/Color" ,d.ProductId ,'USD' as "Currency/CurrencyType" ,1 as "Currency/CurrencyRate" ,d.Total ,d.Fees
where d.Color = 'Red' for xml path('ColorRed') ,type
) -- Red
,(select d.Color as "ColorType/Color" ,d.ProductId ,'USD' as "Currency/CurrencyType" ,1 as "Currency/CurrencyRate" ,d.Total ,d.Fees
where d.Color = 'Black' for xml path('ColorBlack') ,type
) -- Black
from #detail d
where d.AccountNo = a.AccountNo
for xml path('') ,type
)
for xml path('TransactionBasedOnColor') ,type
)
from #table1 a
for xml path('') ,type
January 24, 2012 at 1:39 pm
Sorry for replying late.
And thanks for your awesome solution. It's what I wanted in my actual scenario.
Thanks Again
January 24, 2012 at 1:43 pm
And yes. I have 15-16 different color so right now I am repeating code for 15 times with different color in where condition.
Do you have any better way of doing it without repeating same code 15 times.
Thanks
January 24, 2012 at 2:44 pm
Is it possible to redefine the XML schema and do without the elements ColorWhite, ColorBlack, etc?
If so, some version of the following simplified SQL could apply:
select
a.AccountNo as "Account/AccountNo"
,(select
'20110101' as StartDate
,'20120101' as EndDate
,(select
d.Color as "@Name"
,d.Color as "ColorType/Color"
,d.ProductId
,'USD' as "Currency/CurrencyType"
,1 as "Currency/CurrencyRate"
,d.Total
,d.Fees
from #detail d
where d.AccountNo = a.AccountNo
for xml path('ColorDetail') ,type
)
for xml path('TransactionBasedOnColor') ,type
)
from #table1 a
for xml path('') ,type
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply