July 31, 2008 at 9:59 am
Hi
I am trying to load xml data into target sql server tables. the sample data is given below
the tag names are enclosed in []
[places]
[copy]
[statename 34] new mexico [/statename 34]
[statedesc 34] land of enchanctment[/statedesc 34]
[statename 35] texas[/statename 35]
[ statedesc 35] lone star state[/statedesc 35]
[ statename 36] arizona[/statename 35]
[statedecs36] grand canyon state[/statedesc 36]
[copy]
[places]
The numbers 34,35 and 36 are the ID's for the respectives states. i need to load this data in such a way that the ID's for the state are also extracted and loaded into the table. I am new to sql server. Please help me out with this.
Thanks
July 31, 2008 at 10:19 am
Lookup OPENXML in Books On Line. This converts the XML to a table-like format and then you can do your insert.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 10:21 am
it would extract the data like the state name and decscription but not the id's (34,35,36). I want to extract that also
July 31, 2008 at 10:31 am
Since the ID's are part of the tag I think you would have to do something in .NET or with XSL to parse the tags and create and add the id as an attribute or child, so your xml would be like this (using brackets also):
[state]
[name][/name]
[id][/id]
[/state]
You could also in theory, not really recommended, use the openxml to create a table using select into using the tag names as column names, then use INFORMATION_SCHEMA.COLUMNS to get the id's by parsing the column names.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 31, 2008 at 10:38 am
thanks... i shall try doin that
July 31, 2008 at 11:19 am
Hi Jack
could you provide with an example as to how to use information_schema.column for this. i tried looking online but could not find enough information about it
Thanks
July 31, 2008 at 1:22 pm
When I went to look into using information_schema I think I came up with a solution for you. Try this out:
[font="Courier New"]DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc ='
<places>
<copy>
<statename34>new mexico</statename34>
<statedesc34>land of enchanctment</statedesc34>
<statename35>texas</statename35>
<statedesc35>lone star state</statedesc35>
<statename36>arizona</statename36>
<statedesc36>grand canyon state</statedesc36>
</copy>
</places>
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT localname,
TEXT,
id,
parentid
INTO #states
FROM OPENXML (@idoc, '/places/copy',1)
WHERE localname LIKE 'state%' OR localname LIKE '#text%'
EXEC sp_xml_removedocument @idoc
SELECT
RIGHT(A.localname, 2) AS stateId,
B.TEXT AS statename,
D.TEXT AS statedesc
FROM
#states A JOIN
#states B ON
A.id = B.parentid JOIN
#states C ON
RIGHT(A.localname, 2) = RIGHT(C.localname, 2) AND
A.id <> C.id JOIN
#states D ON
C.id = D.parentid
WHERE
A.localname LIKE '%name%' AND
B.localname = '#text' AND
C.localname LIKE '%desc%' AND
D.localname = '#text'
DROP TABLE #states
[/font]
The end result of that code is this:
stateId statename statedesc
------- -------------------- --------------------
34 new mexico land of enchanctment
35 texas lone star state
36 arizona grand canyon state
Note I had to edit the tags you provided as creating the item as sp_xmlprepare_doc did not like the space between statename and the number.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 10:12 am
thanks.... i shall work on it and let u know how it goes
August 4, 2008 at 6:05 pm
it did work out well. I have another doubt. what if the "ID' number is of varying length for different states. is there any way it could be done or do we have to upload separately the ID's with the same length together.
August 4, 2008 at 6:40 pm
Well, you'd have to find the location of the first numeric character using IsNumeric and then take that position to the right. You would probably want to use a numbers or tally table for this. Look up Tally table on this site for how to use it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2008 at 9:46 am
Thank you for the information jack. i have one more problem dealing with xml data and i have posted it in the forums but there has been no response. i was wondering if you could help me out with that.
the problem which i have posted is:
I have encountered a weird problem while loading XML data. if the text in data contains this symbol ( ' ) then I get an error with my code. but if I remove this symbol from my data then there is no problem in loading.
for e.g. if the sample data is
[places]
[state id = "1"] you're in colorado[/state]
[state id = "2"]I'm in idaho[/state]
[/places]
the error it shows is -- incorrect syntax near 're'
whereas if i have the data as shown below without using the (') symbol then i dont get any errors
[places]
[state id = "1"] you are in colorado[/state]
[state id = "2"]I am in idaho[/state]
[/places]
the code i am using is
declare @idoc int
declare @doc varchar(100)
set @doc='
[places]
[state id = "1"] you're in colorado[/state]
[state id = "2"]I'm in idaho[/state]
[/places]
'
--- Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc output,@doc
--- Execute a SELECT statement that uses the OPENXML rowset provider
select *
from openxml(@idoc,'/places',1)
with (id int,
message ntext 'text()')
exec sp_xml_removedocument @idoc
I dont want to modify my data because it is a huge file . so is there any way i can do it. I am new to this and can't understand why it is happening like this
Thanks
August 5, 2008 at 10:57 am
The single quote " ' " is the string "wrapper" in SQL Server so whenever SQL Server encounters it, it thinks it is the start of a character string. You can use the Replace() function in the Select list of the openxml function. Select Replace(text, '''', '''''') in my example. This will replace a single quote with 2 single quotes which is how you "escape" the quoute character.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2008 at 11:29 am
thanks. actually i am trying to insert the result set obtained from the edge table ( for the first example) into a table already created in the database. but not able to do so. how do i do that?
August 5, 2008 at 11:47 am
sonia (8/5/2008)
thanks. actually i am trying to insert the result set obtained from the edge table ( for the first example) into a table already created in the database. but not able to do so. how do i do that?
I'm not sure what you mean by the edge table, but to insert into an existing table you need to do this:
Insert Into table_name
(
column list
)
Select
column_list
From
table
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 5, 2008 at 12:16 pm
my question is how would i need to insert the data obtained from the following result set of the below code into an already created table. i used the insert statement in the code as highlighted in bold and it gave me an error saying
"string or bianry data would not be truncated"
DECLARE @idoc INT
DECLARE @doc VARCHAR(1000)
SET @doc ='
[places]
[copy]
[statename 34] new mexico [/statename 34]
[statedesc 34] land of enchanctment[/statedesc 34]
[statename 35] texas[/statename 35]
[ statedesc 35] lone star state[/statedesc 35]
[ statename 36] arizona[/statename 35]
[statedecs36] grand canyon state[/statedesc 36]
[/copy]
[/places]
'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT localname,
TEXT,
id,
parentid
INTO #states
FROM OPENXML (@idoc, '/places/copy',1)
WHERE localname LIKE 'state%' OR localname LIKE '#text%'
EXEC sp_xml_removedocument @idoc
INSERT TABLE dbo.statescodes(id,name,desc)
SELECT
RIGHT(A.localname, 2) AS stateId,
B.TEXT AS statename,
D.TEXT AS statedesc
FROM
#states A JOIN
#states B ON
A.id = B.parentid JOIN
#states C ON
RIGHT(A.localname, 2) = RIGHT(C.localname, 2) AND
A.id <> C.id JOIN
#states D ON
C.id = D.parentid
WHERE
A.localname LIKE '%name%' AND
B.localname = '#text' AND
C.localname LIKE '%desc%' AND
D.localname = '#text'
DROP TABLE #states
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply