saving XML in sqlserver issue

Question

saving XML in sqlserver issue

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Dev (12/21/2011)
Koen Verbeeck (12/21/2011)
Phil Parkin (12/21/2011)
But it will continue to cause confusion, and, for some, I suspect, annoyance.
Tsk tsk tsk, Hall Of Fame, is that the christmas spirit? 😀
It might not be his mistake. The way Exp Tag appears in left panel, it looks like a name.
I remember my initial days in SSC I was so surprised that only 2 guys ask questions here Newbie & Grasshopper. 😀

Hahaha 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

GSquared SSC Guru Points: 260824 More actions · Answer 2

engrshafiq4 (12/20/2011)
Sorry for your confusion.
i am asking SSC Eights!
He correct my code in his above post.and that worked for me.
Let me explain the issue
consider i have a table
CREATE TABLE [dbo].[Cities](
[CITY] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COUNTRYID] [int] NOT NULL,
[Keywords] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
Now i have interface where user upload/import xls,xlsx.so first i convert this data to xml(this is an order from my teamlead due to server restriction) and then pass it to storeprocedure as param.there inside i save all xml elements into table fields.
Now i above case i want to have xml element for COUNTRYID must be integer value and not null.similarly the keywords must be characters.....hope you got my point

You can validate XML data types with an XSD. There's data about how to do that in Books Online (same data on http://www.msdn.com).

HOWEVER, and this is important, if you're validating the XML as you insert it into the table, that's WAY too late in the process. The only thing you can at that point is raise an SQL Server error because of XML schema violation, and the application will have to handle that error and do something with it. The better place to validate XML is before it gets submitted to the stored procedure in the first place.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 3

GSquared (12/21/2011)
engrshafiq4 (12/20/2011)
Sorry for your confusion.
i am asking SSC Eights!
He correct my code in his above post.and that worked for me.
Let me explain the issue
consider i have a table
CREATE TABLE [dbo].[Cities](
[CITY] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[COUNTRYID] [int] NOT NULL,
[Keywords] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
Now i have interface where user upload/import xls,xlsx.so first i convert this data to xml(this is an order from my teamlead due to server restriction) and then pass it to storeprocedure as param.there inside i save all xml elements into table fields.
Now i above case i want to have xml element for COUNTRYID must be integer value and not null.similarly the keywords must be characters.....hope you got my point
You can validate XML data types with an XSD. There's data about how to do that in Books Online (same data on http://www.msdn.com).
HOWEVER, and this is important, if you're validating the XML as you insert it into the table, that's WAY too late in the process. The only thing you can at that point is raise an SQL Server error because of XML schema violation, and the application will have to handle that error and do something with it. The better place to validate XML is before it gets submitted to the stored procedure in the first place.

yes good idea...i ll try to validate in application like asp.net

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 4

Hi all,

as in this post i am converting my XLS file to xml and pass to store procedure ...till here it is working..i can save all xml data...now issue is how i can pass more data with this xml and how i ll read it

here is my code

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO TEST ([name]

)

SELECT *

FROM OPENXML (@idoc, 'NewDataSet/Table', 1)

WITH ([Name] [varchar](50) 'Name'

)

EXECUTE sp_xml_removedocument @idoc