April 18, 2012 at 4:40 am
I am uploading xls to sqlserver as xml for bulk upload...it works fine for me till now...now i want to save userID with each data of xml....
here is my code
ALTER PROCEDURE ABC
@xml varchar(max),
@userid varchar(50)
AS
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
INSERT INTO TESTCITY (
[CITY]
,[COUNTRYID]
,[Keywords]
,[DialCode]
)
SELECT *
FROM OPENXML (@idoc,@chk , 1)
WITH ([CITY] [varchar](50) 'CITY',
[COUNTRYID] INT 'COUNTRYID',
[Keywords] [varchar](50) 'Keywords',
[DialCode] [varchar](50) 'DialCode')
EXECUTE sp_xml_removedocument @idoc
END
NOW i want to insert userID too...my table will look like this
INSERT INTO TESTCITY (
[CITY]
,[COUNTRYID]
,[Keywords]
,[DialCode]
,[userID]
)
Now how i will assign @userid.....Remember that userID is not included inside @xml...that have been passed a seperete variable
April 18, 2012 at 11:35 pm
You can try this:
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
INSERT INTO TESTCITY (
[CITY]
,[COUNTRYID]
,[Keywords]
,[DialCode]
,UserID
)
SELECT *, @userid
FROM OPENXML (@idoc,@chk , 1)
WITH ([CITY] [varchar](50) 'CITY',
[COUNTRYID] INT 'COUNTRYID',
[Keywords] [varchar](50) 'Keywords',
[DialCode] [varchar](50) 'DialCode')
EXECUTE sp_xml_removedocument @idoc
END
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 19, 2012 at 1:43 am
@dwain.c:
That work very Fine...Now i want only one change....
as you can see that in @xml we have CITY which is NVARCHAR so i want to get ID of each in a table...like if CITY="ABC" then i want like this
select ID from table where CITY ="ABC" OR @City variable
and then want to insert this ID into field DIALCODE which is in my same table.
this is for each record..is this possible?
April 19, 2012 at 3:01 am
Now i want only one change....
as you can see that in @xml we have CITY which is NVARCHAR so i want to get ID of each in a table...like if CITY="ABC" then i want like this
select ID from table where CITY ="ABC" OR @City variable
and then want to insert this ID into field DIALCODE which is in my same table.
this is for each record..is this possible?
Let me see if I can rephrase to see if I understand.
1. CITY is actually being extracted as VARCHAR from the XML but that is probably not relevant.
2. And I don't think you mean you want to INSERT the DIALCODE, but rather you want to UPDATE the city record's DIALCODE with data from ID?
3. To put it another way, for city ABC you want to update the DIALCODE column with the ID column for that record?
4. Is ID in the same or a different table? Please provide DDL for that table and the original one.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 19, 2012 at 3:09 am
dwain.c (4/19/2012)
Now i want only one change....
as you can see that in @xml we have CITY which is NVARCHAR so i want to get ID of each in a table...like if CITY="ABC" then i want like this
select ID from table where CITY ="ABC" OR @City variable
and then want to insert this ID into field DIALCODE which is in my same table.
this is for each record..is this possible?
Let me see if I can rephrase to see if I understand.
1. CITY is actually being extracted as VARCHAR from the XML but that is probably not relevant.
2. And I don't think you mean you want to INSERT the DIALCODE, but rather you want to UPDATE the city record's DIALCODE with data from ID?
3. To put it another way, for city ABC you want to update the DIALCODE column with the ID column for that record?
4. Is ID in the same or a different table? Please provide DDL for that table and the original one.
Ok let me explain in a simple way....
actually i am uploading xls file to server...i am passing data as xml and inside xml i have COUNTRYID (if you look at the previous post)...Now while entering data in xls no one knows COUNTRYID,He can know only CountryName...if we pass countryName in XML instead of countryid and here in storeprocedure to find out its countryid against that countryname and save it?
so simply i have countryId in table and in data variable i have countryname....
also countries table is totally different....
April 19, 2012 at 3:36 am
OK that makes sense.
You can do that in the original query by doing a LEFT OUTER JOIN with the country table ON the country name field. However, that's generally not a good practice in case there are spelling errors in the XML, unless of course the XML is populated by a dropdown list (then you should be OK).
When the countryID from the country table comes up as NULL from the JOIN, you'll need to decide what to do (maybe use a CASE to fill it in from the XML).
I would also get rid of the * in your SELECT (another bad practice) and replace it with field names. You'll need to do that anyway because * (after doing the JOIN) will return all fields from both tables and thus too many for your INSERT statement to handle.
Let me know if you understand and get it working.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 19, 2012 at 4:27 am
How it could be like this?
BEGIN
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
INSERT INTO TESTCITY (
[CITY]
,[COUNTRYID]
,[Keywords]
,[DialCode]
,UserID
)
SELECT CITY,COUNTRYID=(select Id from Country where CountryName=CountryName),Keywords,DialCode, @userid
FROM OPENXML (@idoc,@chk , 1)
WITH ([CITY] [varchar](50) 'CITY',
[CountryName] [varchar](50) 'CountryName',
[Keywords] [varchar](50) 'Keywords',
[DialCode] [varchar](50) 'DialCode')
EXECUTE sp_xml_removedocument @idoc
END
How countryname can be available to it?i do not understand here
April 19, 2012 at 6:52 am
You need to do the LEFT OUTER JOIN after the WITH clause of the original INSERT query.
Sorry but I'm a bit sick tonight so I may not be able to help you further today.
If you need me to solve this with the explicit code, here is what I would need:
- A sample XML document.
- DDL to set up both tables
- Some INSERTS to populate both tables (well maybe just the Country table might do)
The elaboration of the fields extracted from the XML on the SELECT and after ON is not something I have memorized. I'd need to work with it to get it right.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 19, 2012 at 7:43 am
The arrangement of the fields going to confuse me...we must have a fields in a series....some fields are coming from xml and some will come from country..really confused...the ID comes from Country table shold be on second number mean will be inside xml variable...so confusion again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply