473,287 Members | 2,263 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

OPENXML question

I've got thousands of XML docs and have to import those to the DB. And
I am having problem with getting some values because of tricky XML
format.

I didn't create this XML format and I don't like this format, OOP snob
did. And I(DBA) have to look after their mess.
I've got XML doc like this :
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>
First, I did following :

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '/customer',2)
WITH (customer_id int,
first_name varchar(50),
last_name varchar(50),
location varchar(50))

Then returned this which is not bad.

customer_id | first_name |last_name |location
12345 | Jason | Varitek |Boston MA

But I need to return city and state in separated columns(I wish OOP
snobs made these as attributes in location element, instead of
elements). And I tried following and returned everything NULL.

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<customer>
<customer_id>12345</customer_id>
<first_name>Jason</first_name>
<last_name>Varitek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT *
FROM OPENXML (@idoc, '/customer/customer_id',2)
WITH (customer_id int '../@customer_id',
first_name varchar(50) '../@first_name',
last_name varchar(50) '../@last_name',
city varchar(50) '@city',
state varchar(50) '@state')
Does anybody have any idea to get value of city & state elements in
separated columns?

Thanks

Jimmy

Jul 23 '05 #1
2 1628
SELECT *
FROM OPENXML (@idoc, '/customer',2)
WITH (customer_id int,
first_name varchar(50),
last_name varchar(50),
city varchar(50) './location/city',
state varchar(50) './location/state')

Jul 23 '05 #2
Thanks.

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: JM | last post by:
I have a SP set up to take an input param and then fire the following sp_xml_preparedocument OPENXML (With a select statement) sp_xml_removedocument This works fine with the XML syntax that...
1
by: jrd | last post by:
The below code is only pulling the outcome_id value, but is pulling back null for the other fields in the xml string, any ideas on what is going on? -- Prepare xml data to be transfered into an...
1
by: Sandy | last post by:
Hi, My application writes data into sql server. Currently it converts data into XML (an in memory XML string) and write using OPENXML. I want to know if i write it to a csv file and use BCP,...
0
by: Micke | last post by:
On a code behing page to an aspx page I want to open a xml file in Excel and save it as an Excel file. I have following code that is working: Dim oExcel As New Excel.Application Dim oBook As...
1
by: figital | last post by:
I am researching the use of OpenXml for doing mass updates/inserts. Does anyone know how this procedure works as far as sql injection is concerned? I've always been taught to use sp's with...
2
by: Shilpa | last post by:
Hi All, I want to pass XML and the data in the XML should be stored in the tables of the database. However, I do not want to use the OpenXML statement. Please let me know. Regards, Shilpa
9
by: anupamjain | last post by:
Hi, After 2 weeks of search/hit-and-trial I finally thought to revert to the group to find solution to my problem.(something I should have done much earlier) This is the deal : On a JSP...
1
by: yingwen | last post by:
I have a xml similar like this: <Member ID="123"> <DateBorrowed>11-01-2006</DateBorrowed> <Book ID="222" Title="ABC"> <Category> Fiction</Category> </Book>...
5
by: emsik1001 | last post by:
I'm trying to upload XML into SQL Server 2000. I've never used openxml and I'm struggling with it. Below is the procedure with data. DECLARE @idoc int DECLARE @doc varchar (1000) SET @doc...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.