import/convert xml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

import/convert xml

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 15:02:05

i have xml that looks like this that i have to somehow get into sql server. what's the easiest way? i haven't tried some ways i see online and get errors.... keep in mind i have to use the ssn to assign each note in the system to a specific person.

<CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE datetime="11/12/03 03:31 PM" code="">test</NOTE>
</NOTES>
</CLIENT>

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 15:22:40

i get the following error when using openxml:

XML parsing error: The following tags were not closed: CLIENT, NOTES, NOTE.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-10 : 15:52:16

perhaps it might help us to help you if you show us the statement you tried?

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 15:52:59

Think you need to wrap it "all" up in <ROOT> Nodes

I wish someone would start an Official XML Rant Thread.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 16:10:19

here is what i have so far. this will give me the ssn and datetime, but not the note. how do i get that data? i'm using test data below. my actual data has ssn's in it i can't post. the xml is VERY long. so once i actually use the actual i'm sure i'll run into more errors. are there any characters i need to replace in the xml that will cause errors?

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
</NOTES>
</CLIENT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/CLIENT/NOTES/NOTE',0)
WITH (ssn varchar(15) '../../@ssn',dtcreated datetime,note varchar(500))

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 16:15:12

declare @idoc int
declare @doc varchar(1000)
set @doc ='<ROOT>
<CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
</NOTES>
</CLIENT>
<CLIENT ssn="111111123" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
</NOTES>
</CLIENT></ROOT>
'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/CLIENT/NOTES/NOTE',0)
WITH (ssn varchar(15) '../../@ssn',dtcreated datetime,note varchar(500))

I wish someone would start an Official XML Rant Thread.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-10 : 16:29:26

You do not need root. You say "the xml is VERY long" How Long??

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 16:33:57

now i have:

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT><CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/18</NOTE>
</NOTES>
</CLIENT>
<CLIENT ssn="111111112" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/18</NOTE>
</NOTES>
</CLIENT></ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/CLIENT/NOTES/NOTE')
WITH (ssn varchar(15) '../../@ssn',dtcreated datetime,NOTE varchar(500) 'NOTE')

which gives me:
111111111 2003-11-12 15:31:00.000 NULL
111111111 2003-11-13 15:31:00.000 NULL
111111112 2003-11-12 15:31:00.000 NULL
111111112 2003-11-13 15:31:00.000 NULL

i just am not getting the note now.....

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 16:36:07

just over 2mb. 23000 lines......

quote:
Originally posted by ehorn

You do not need root. You say "the xml is VERY long" How Long??

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 16:44:49

now i have:

declare @idoc int
declare @doc varchar(1000)
set @doc ='
<ROOT><CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">1</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">2</NOTE>
</NOTES>
</CLIENT>
<CLIENT ssn="111111112" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">3</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">4</NOTE>
</NOTES>
</CLIENT></ROOT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/CLIENT/NOTES/NOTE')
WITH (ssn varchar(15) '../../@ssn',NOTE varchar(500) '../NOTE',dtcreated datetime)

gives me:
111111111 1 2003-11-12 15:31:00.000
111111111 1 2003-11-13 15:31:00.000
111111112 3 2003-11-12 15:31:00.000
111111112 3 2003-11-13 15:31:00.000

correct number of notes, but the first note for each client is just being repeated, it's not giving me all the note values....

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-10 : 16:45:08

There is no elegant way to perform this in the current version of SQL Server (Yukon will make this much easier). You are dealing with a text, ntext datatype and would need to use cursors and dynamic SQL to perform this xml parsing (Its not pretty). An alternative would be to use another tool (like c# or your favorite language) to parse and import the data.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 16:51:09

y is that? if that's true, can someone point me in the right direction to use another tool to get this done for me?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-10 : 16:59:19

quote:
Originally posted by mikejohnson

y is that?

Well, let me ask you, how are you getting the xml into sp_xml_preparedocument? Are you going to be passing it in from an application. Are you storing the xml in a text field of a table?, etc..?

The only way to perform this would be to pass the xmldoc as a text value, from an outside call (ie ADO.NET, etc.) into an sp which accepts a text input param. Then call sp_xml_preparedocument.

Also to get then note element out you need to specify the xpath like so '../..'

SELECT *
FROM OPENXML (@idoc, '/CLIENT/NOTES/NOTE')
WITH (
	ssn varchar(15) '../../@ssn',
	dtcreated datetime '@dtcreated',
	note varchar(500) '../..')

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 17:08:25

quote:
You do not need root

Without <Root> I get

XML parsing error: Only one top level element is allowed in an XML document.

I can't argue it "IS" needed but in this case it worked, why or when is it not needed?

I wish someone would start an Official XML Rant Thread.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-10 : 17:16:39

quote:
Without <Root> I get XML parsing error: Only one top level element is allowed in an XML document.

All sp_xml_preparedocument requires is well-formed xml. My guess is you forgot to remove the closing </root> tag.

>>why or when is it not needed?

As mentioned, it is never needed. What is needed is well-formed xml

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 17:18:58

[code]declare @idoc int
declare @doc varchar(1000)
set @doc ='
<CLIENT ssn="111111111" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/18</NOTE>
</NOTES>
</CLIENT>
<CLIENT ssn="111111112" lastName="test" firstName="test">
<NOTES>
<NOTE dtcreated="11/12/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/17</NOTE>
<NOTE dtcreated="11/13/03 03:31 PM" code="">indicated that wrong stop payment was made, will pay on 11/18</NOTE>
</NOTES>
</CLIENT>'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/CLIENT/NOTES/NOTE')
WITH
(ssn varchar(15) '../../@ssn',
dtcreated datetime '@dtcreated',
note varchar(500) '../..')
[/code]

Possibly... could you run the above?

I wish someone would start an Official XML Rant Thread.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 17:23:23

y am i not getting the correct note fields?

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 17:24:20

with the code you just posted i got:

XML parsing error: Only one top level element is allowed in an XML document.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2005-01-10 : 17:25:20

....and

quote:
As mentioned, it is never needed. What is needed is well-formed xml

I thought they were the same thing! Well formed "required" one and only one Root element.

I wish someone would start an Official XML Rant Thread.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-10 : 17:28:00

The poster has one in his example. It is called <CLIENT>
It should not require an additional top-level element.

So let me rephrase my statement. When do you need to use <ROOT>??

When you are receiving an non well-formed xml with no top-level element you need to add a top-level element. Lets call it <SITKA> :)

mikejohnson
Posting Yak Master

153 Posts

Posted - 2005-01-10 : 17:29:25

although it's funny you guys are argueing about this, let's focus on helping me here please :) still not getting the correct note values

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources