problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx

problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx

Problem with unit design

Hi

In my inventory system I have a table Units which has 3 fields - UnitID, UnitName, Quantity.

Suppose I purchase any product. I will input that in my Receives table with a Quantity and Unit field. When I issue a product I'll input that in Issues table with a Quantity and Unit field.

Now problem is I will purchase products with one unit like Box and issue product in another unit like piece. A box may contain some pieces.

How can I provide this facility with the following tables ? I want something like, when quering current stock, it should show -

Product Quantity

-- --

Pencil 16 Box and 3 Piece

And while issueing and purchasing I want to calculate the stock. In the Unit table I want to define each unit with the containing smallest quantity, Like -

Dozen 12

Packet 20

Need help!

Regards

Kapalic

Option 1:

Always store the peices on your table. Then you can represent it any unit.

Product QuantityInPieces DisplayUnit

Option 2:

You can change the datatype of Quantity from int to float.

[Box/Dozen].[Pieces]

12.5

12 Box 5 Pieces if displayunit = Box

The floor is always Box/Dozen it may be identified from the Unit column. The decimal numbers always indicate the pieces..

BUT I recommand to use OPTION 1.

Code Snippet

Declare @.Units Table
(
UnitId int,
UnitName varchar(100),
UnitQty int
)

Insert Into @.Units Values(1,'Box',20);
Insert Into @.Units Values(2,'Dozen',12);

Declare @.Product Table
(
product varchar(100),
qtyinpieces int,
displayunit int
)

Insert Into @.Product Values('One', 122,1);
Insert Into @.Product Values('Two', 234,2);
Insert Into @.Product Values('Three', 24,2);
Insert Into @.Product Values('Four', 50,1);

Select
P.product
,Convert(varchar,P.qtyinpieces/UnitQty) + ' ' + UnitName + Case When P.qtyinpieces%UnitQty <> 0 Then ' ' + Cast(P.qtyinpieces%UnitQty as Varchar) + ' Pieces' Else '' End
From
@.Product P Join @.Units U on P.displayunit=U.UnitId