saving XML in sqlserver issue

Question

Post reply

saving XML in sqlserver issue

MuhammadShafiq

SSCrazy

Points: 2509
More actions
December 15, 2011 at 5:42 am

#252151

Hi all,
Its urgent please replay me.
i am passing XML as string to store procedure and i want to save elements of xml in table.here is my code
alter PROCEDURE usp_be_insertXML
-- Add the parameters for the stored procedure here
@xml varchar(1000)
AS
BEGIN
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =@xml
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
BEGIN TRY
INSERT INTO testing ([Name] ,[Fname] )
SELECT Column1,Column2
FROM OPENXML (@idoc, 'NewDataSet/Sheet1', 1)
WITH ([Column1] [nvarchar](50),
[Column2] [nvarchar](50))
END TRY
BEGIN CATCH
END CATCH
EXECUTE sp_xml_removedocument @idoc
END
GO
i have one table testing having fields Name and FName
here is my xml
<NewDataSet>
<Sheet1>
<Column1>Name</Column1>
<Column2>Fname</Column2>
</Sheet1>
<Sheet1>
<Column1>khan</Column1>
<Column2>dd</Column2>
</Sheet1>
<Sheet1>
<Column1>mytest</Column1>
<Column2>ff</Column2>
</Sheet1>
</NewDataSet>
BUt is saves NULL in table.any help?

Viewing 15 posts - 1 through 15 (of 18 total)

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Change

SELECT *

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

WITH ([Column1] [varchar](50),

[Column2] [varchar](50) )

to

SELECT *

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

WITH ([Column1] [varchar](50) 'Column1',

[Column2] [varchar](50) 'Column2')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

GSquared SSC Guru Points: 260824 More actions · Answer 2

Any reason not to simplify this by using the XML datatype? Per the forum you posted in, you're using SQL 2005, which supports XML quite nicely.

- 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

@SSC:

Good,this worked fine for me,now the only issue is i need checks now.

For example i want to have first column of integer data and second must not be null and third of varchar etc etc.how it could be?

Secondly there is issue with special character.what i do with that?

GSquared SSC Guru Points: 260824 More actions · Answer 4

I'm assuming you're asking me, but I'm not clear on what you're asking. XML doesn't have columns, it has elements. Is that what you mean? If so, I believe you can validate XML against an XSD for data rules.

- 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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

GSquared (12/20/2011)
I'm assuming you're asking me, but I'm not clear on what you're asking. XML doesn't have columns, it has elements. Is that what you mean? If so, I believe you can validate XML against an XSD for data rules.

I guess he is asking to Mark (SSC Eights!) not you (SSChampion)...

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

Dev (12/20/2011)
GSquared (12/20/2011)
I'm assuming you're asking me, but I'm not clear on what you're asking. XML doesn't have columns, it has elements. Is that what you mean? If so, I believe you can validate XML against an XSD for data rules.
I guess he is asking to Mark (SSC Eights!) not you (SSChampion)...

...and I don't know what he's asking either.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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

...and I don't know what he's asking either.

Come on Mark, tell us about the special character 🙂

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

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 8

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

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

engrshafiq4 (12/20/2011)
Sorry for your confusion.
i am asking SSC Eights!
...........................

Please take a look at one of your posts and, if you decide that you are happy being referred to as 'Valued Member', I guess it's only fair that you continue to refer to others by their experience descriptor rather than by their name. But it will continue to cause confusion, and, for some, I suspect, annoyance.

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

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

I know Valued Member, that I'm not SSC Eights, but I'll try to post a suggestion:

validate your XML with an XSD schema. That's what it is meant to do.

An example:

http://blog.sqlauthority.com/2009/12/02/sql-server-validate-an-xml-document-in-tsql-using-xsd-by-jacob-sebastian/

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

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? 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 12

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. 😀

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 13

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. 😀

Yes, you are Right,i was confused with Exp Tag.anyway i ll improve with time.i need one more help

How can i mark my questions as answers?i have alot of questions which are solved now.

as we can do in http://forums.asp.net

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 14

engrshafiq4 (12/21/2011)

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. 😀

Yes, you are Right,i was confused with Exp Tag.anyway i ll improve with time.i need one more help

How can i mark my questions as answers?i have alot of questions which are solved now.

as we can do in http://forums.asp.net[/quote%5D

A small note of completion would be fine. 🙂