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
JimmySELECT *
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')|||Thanks.
