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