MSSQL读取xml字符串到临时表

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

　　

转载于:https://www.cnblogs.com/lijunhao/p/5564224.html