xml类型转换列表显示 SQL查询

数据库中存在字段类型为xml 的数据，

现举例 xml 字段存储的数据为：

<MortgageInfoShipList>
<ITEMS>
<ITEM>
<ShipName>船名2</ShipName>
<Location>处所2</Location>
<RegisterLocation>船籍港2</RegisterLocation>
<ShipType>类型2</ShipType>
<ReadNum>识别号2</ReadNum>
<RegisterNum>登记号2</RegisterNum>
<CallNum>呼号2</CallNum>
<InsuranceNum>单号2</InsuranceNum>
<InsuranceTime>2016-08-22至2016-08-22</InsuranceTime>
<Weight>12</Weight>
<Age>12</Age>
<Owner>hqq1</Owner>
</ITEM>
<ITEM>
<ShipName>船名1</ShipName>
<Location>处所1</Location>
<RegisterLocation>船籍港1</RegisterLocation>
<ShipType>类型1</ShipType>
<ReadNum>识别号1</ReadNum>
<RegisterNum>登记号1</RegisterNum>
<CallNum>呼号1</CallNum>
<InsuranceNum>单号1</InsuranceNum>
<InsuranceTime>2016-08-22至2016-08-22</InsuranceTime>
<Weight>10</Weight>
<Age>11</Age>
<Owner>hqq</Owner>
</ITEM>
</ITEMS>
</MortgageInfoShipList>

==============要将他显示为列表===================

 注意：因为数据库中存储的xml 类型没有版本号和编码格式，所以转换之前要先拼接下  ‘<?xml version="1.0" encoding="gb2312" ?>’

 

--抵押物信息
DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc =(SELECT '<?xml version="1.0" encoding="gb2312" ?>'+ RTRIM(CAST(cast(MortgageInfoShip as varchar(8000)) as VARCHAR(max))) FROM CustomerExt WHERE CustCode='BL1608050936214438')
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT ROW_NUMBER()over(order by InsuranceTime) AS Rows,*
FROM OPENXML (@idoc, '/MortgageInfoShipList/ITEMS/ITEM',2)
WITH (
ShipName VARCHAR(20) ,--船名
ShipType VARCHAR(20),--船类型
ReadNum VARCHAR(20) ,--识别号
RegisterNum VARCHAR(20),--注册号
RegisterLocation VARCHAR(30) ,--船籍港
InsuranceTime VARCHAR(30))
EXEC sp_xml_removedocument @idoc

 

查询后