SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Table to xml --Urgent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pushp82
Yak Posting Veteran

83 Posts

Posted - 09/14/2013 :  03:25:49  Show Profile  Reply with Quote
Hi Friends,

I'm facing an issue with table data to xml.
There is a table as below:

id code price qty
15757 CW 230 2
15758 CW 250 2
8389 MC 990 3
8387 MC 660 2
8399 MC 1020 3
98563 CT 500 1
98564 CT 520 2

I need an output as :

<orders>
<order>
<cpncode>0</cpncode>
<OTLCODE>CW</OTLCODE>
<item1d>
<id>15757</id>
<qty>2</qty>
<itemprice>230</itemprice>
<addon></addon>
</item1d>
<item1d>
<id>15758</id>
<qty>2</qty>
<itemprice>250</itemprice>
<addon></addon>
</item1d>
</order>
<order>
<cpncode>0</cpncode>
<OTLCODE>MC</OTLCODE>
<item1d>
<id>8389</id>
<qty>3</qty>
<itemprice>990</itemprice>
<addon></addon>
</item1d>
<item1d>
<id>8387</id>
<qty>2</qty>
<itemprice>660</itemprice>
<addon></addon>
</item1d>
</order>
<order>
<cpncode>0</cpncode>
<OTLCODE>CT</OTLCODE>
<item1d>
<id>98563</id>
<qty>1</qty>
<itemprice>500</itemprice>
<addon></addon>
</item1d>
<item1d>
<id>98564</id>
<qty>2</qty>
<itemprice>520</itemprice>
<addon></addon>
</item1d>
</order>
</orders>

What I did :
declare @xmldoc1 as varchar(max)
SET @xmldoc1=(SELECT 0 AS cpncode,'MC' as OTLCODE ,
(
SELECT CRT_ITM_ID AS id,1 AS qty,PRICE AS itemprice,0 AS addon FROM @TABLE
FOR XML PATH('item1d'), TYPE
)
FOR XML PATH('order'), ROOT('orders'))

PRINT @xmldoc1

I'm moving around otlcode and order tag. Please help. Urgent
A <order> tag is a bunch of one <OTLCODE> and <orders> is root tag

Edited by - pushp82 on 09/14/2013 03:29:50

MuMu88
Aged Yak Warrior

547 Posts

Posted - 09/14/2013 :  13:06:49  Show Profile  Reply with Quote
something like this:

-- TEST DATA:
DECLARE @Temp TABLE(id INT, code VARCHAR(2), price INT, qty INT);
INSERT INTO @Temp VALUES
(15757, 'CW',  230, 2),
(15758, 'CW', 250, 2),
(8389, 'MC', 990, 3),
(8387, 'MC', 660, 2),
(8399, 'MC', 1020, 3),
(98563, 'CT', 500, 1),
(98564, 'CT', 520, 2);


--QUERY:
SELECT 0 as cpncode, code AS OTLCODE, 
            (SELECT id, qty,PRICE AS itemprice, 0 AS addon FROM @Temp T 
		WHERE T.code = TT.code and T.id = TT.id 
		ORDER BY code, id
		FOR XML PATH('itemid'), TYPE ) 
FROM @Temp TT ORDER BY TT.code
FOR XML PATH('order'), ROOT('orders')

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/15/2013 :  03:42:49  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

something like this:

-- TEST DATA:
DECLARE @Temp TABLE(id INT, code VARCHAR(2), price INT, qty INT);
INSERT INTO @Temp VALUES
(15757, 'CW',  230, 2),
(15758, 'CW', 250, 2),
(8389, 'MC', 990, 3),
(8387, 'MC', 660, 2),
(8399, 'MC', 1020, 3),
(98563, 'CT', 500, 1),
(98564, 'CT', 520, 2);


--QUERY:
SELECT 0 as cpncode, code AS OTLCODE, 
            (SELECT id, qty,PRICE AS itemprice, 0 AS addon FROM @Temp T 
		WHERE T.code = TT.code and T.id = TT.id 
		ORDER BY code, id
		FOR XML PATH('itemid'), TYPE ) 
FROM @Temp TT ORDER BY TT.code
FOR XML PATH('order'), ROOT('orders')




A small tweak to match OPs output format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pushp82
Yak Posting Veteran

83 Posts

Posted - 09/20/2013 :  08:43:27  Show Profile  Reply with Quote
Well thanks, guys I did it with while loop and added required string but I will consider the above solution once I will done all development and will continue for performance tuning.
Thanks mumu88 and visakh16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/22/2013 :  02:06:37  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000