DECLARE @hdoc int

DECLARE @doc xml

SET @doc ='<CityValueSet>

<CityItem>

<CityId>201</CityId><CityName>北京</CityName><EngName>beijing</EngName><Level>1</Level><ParentId>2</ParentId><Memo1>1</Memo1><Memo2>1</Memo2><Memo3></Memo3><CityFullName>北京北京市</CityFullName></CityItem>

<CityItem>

<CityId>1801</CityId><CityName>呼和浩特</CityName><EngName>huhehaote</EngName><Level>2</Level><ParentId>18</ParentId><Memo1>2</Memo1><Memo2>2</Memo2><Memo3></Memo3><CityFullName>内蒙古自治区呼和浩特市</CityFullName></CityItem>

</CityValueSet>

'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc 

SELECT *

FROM openxml(@hdoc,'/CityValueSet/CityItem',2)

WITH (CityId int,CityName VARCHAR(200),EngName varchar(100),Level int,ParentId int,Memo1 int,Memo2 int,Memo3 int,CityFullName varchar(200))