XML and SQL - 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

XML and SQL

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-02-25 : 23:13:51

I have one SP. Input for that sp is XML as follow

<Result>
<Modeloutput Type="Type1">
<ID>101</ID>
<Group>1</Group>
<Age>0</Age>
<Index>6</Index>
<MinCoupon>6.1234</MinCoupon>
<DiscountAmt>10</DiscountAmt>
<Balance />
</Modeloutput>
<Modeloutput Type="Type2">
<ID>101</ID>
<Group>1</Group>
<Age>0</Age>
<Index>6</Index>
<NewCoupon>6.53658</NewCoupon>
<DiscountAmt>20</DiscountAmt>
<Balance />
</Modeloutput>
</Result>

I have one table tb1 ,where i waant to insert above data for 2 types Type1 and Type2.
So there will be 2 rows for 2 type. But for Type1 Coupon column value is from <MinCoupon> and
for Type2 Coupon column value is from <NewCoupon>. So plz let me know how I can handle this.
Also for <Balance /> there is no data. for this I am getting error " convertion error - nvarchar to numeric"

Table stucture is as follow and data should be

tb1

ID Type Group Age Index Coupon DisAmt Balance
101 Type1 1 0 6 6.1234 10 0
101 Type2 1 0 6 6.53658 20 0

Plz tell me how I can handele above 2 situations. How I can insert 2 rows with different data for 2 types in one table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 03:26:36

[code]DECLARE @Sample TABLE
(
ID INT,
[Type] VARCHAR(20),
[Group] INT,
Age INT,
[Index] INT,
Coupon DECIMAL(10, 5),
DisAmt INT,
Balance INT
)

INSERT @Sample
SELECT 101, 'Type1', 1, 0, 6, 6.1234 , 10, 0 UNION ALL
SELECT 101, 'Type2', 1, 0, 6, 6.53658, 20, 0

SELECT *
FROM @Sample

SELECT [Type] AS [@Type],
ID,
[Group],
[Age],
[Index],
Coupon AS MinCoupon,
DisAmt AS DiscountAmt,
Balance
FROM @Sample
FOR XML PATH('Modeloutput'),
ROOT('Result')[/code]

E 12°55'05.63"
N 56°04'39.26"

jbp_j
Starting Member

24 Posts

Posted - 2009-02-26 : 04:41:09

Hi,
I think this is u r input and output.

try this one it's get u r required output.

declare @xml xml
set @xml = '<Result>
<Modeloutput Type="Type1">
<ID>101</ID>
<Group>1</Group>
<Age>0</Age>
<Index>6</Index>
<MinCoupon>6.1234</MinCoupon>
<DiscountAmt>10</DiscountAmt>
<Balance />
</Modeloutput>
<Modeloutput Type="Type2">
<ID>101</ID>
<Group>1</Group>
<Age>0</Age>
<Index>6</Index>
<NewCoupon>6.53658</NewCoupon>
<DiscountAmt>20</DiscountAmt>
<Balance />
</Modeloutput>
</Result>'

select T.DATA.value('@Type', 'Varchar(50)') AS Type,
T.DATA.value('ID[1]','INT') as Id,
T.DATA.value('Group[1]','INT') as Group1,
T.DATA.value('Age[1]','INT') as Age,
T.DATA.value('Index[1]','int') as Index1,
ISNULL(T.DATA.value('MinCoupon[1]','decimal(18,4)'),T.DATA.value('NewCoupon[1]','decimal(18,4)')) as Coupon,
T.DATA.value('DiscountAmt[1]','decimal(18,4)') as DiscountAmt,
T.DATA.value('Balance[1]','int') as Balance
from @xml.nodes('Result/Modeloutput') as T(DATA)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources