Table to xml --Urgent - 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

General SQL Server Forums

New to SQL Server Programming

Table to xml --Urgent

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-09-14 : 03:25:49

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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-09-14 : 13:06:49

something like this:
[CODE]
-- 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')

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-15 : 03:42:49

quote:
Originally posted by MuMu88

something like this:
[CODE]
-- 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')

[/CODE]

A small tweak to match OPs output format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pushp82
Yak Posting Veteran

83 Posts

Posted - 2013-09-20 : 08:43:27

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:06:37

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources