OPENXML question

[Bostonasian]

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>12 345</customer_id>
<first_name>Jas on</first_name>
<last_name>Vari tek</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>12 345</customer_id>
<first_name>Jas on</first_name>
<last_name>Vari tek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'
EXEC sp_xml_prepared ocument @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>12 345</customer_id>
<first_name>Jas on</first_name>
<last_name>Vari tek</last_name>
<location>
<city>Boston</city>
<state>MA</state>
</location>
</customer>'

EXEC sp_xml_prepared ocument @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

[markc600@hotmail.com]

Re: OPENXML question

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')

[Bostonasian]

Re: OPENXML question

Thanks.