sql server xml 解析成table

来源：互联网发布：java mmap 编辑：程序博客网时间：2024/05/19 15:41

普通xml

DECLARE @ItemMessage XML

SET @ItemMessage=N'<prolist>
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
</prolist>'

SELECT T.c.value('(proid/text())[1]','INT') proid,
T.c.value('(buynumber/text())[1]','INT') buynumber,
T.c.value('(promoney/text())[1]','decimal(18, 2)') promoney,
T.c.value('(couponmoney/text())[1]','decimal(18, 2)') couponmoney,
T.c.value('(prototalamount/text())[1]','decimal(18, 2)') prototalamount,
T.c.value('(procost/text())[1]','decimal(18, 2)') procost,
T.c.value('(propaytypemoney/text())[1]','nvarchar(500)') propaytypemoney,
T.c.value('(probenefitid/text())[1]','INT') probenefitid,
T.c.value('(muserid/text())[1]','INT') muserid,
T.c.value('(protype/text())[1]','INT') protype,
T.c.value('(proserverce/text())[1]','nvarchar(200)') proserverce,
T.c.value('(proisreturn/text())[1]','INT') proisreturn,
T.c.value('(promark/text())[1]','nvarchar(500)') promark

FROM @ItemMessage.nodes('/prolist/pro') AS T(c)

解析带命名空间的xml“http://cd.love.com/SOA”

DECLARE @ItemMessage XML

SET @ItemMessage=N'<prolist xmlns="http://cd.love.com/SOA">
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
<pro>
<proid>1</proid>
<buynumber>50</buynumber>
<promoney>50</promoney>
<couponmoney>50</couponmoney>
<prototalamount>50</prototalamount>
<procost>50</procost>
<propaytypemoney>50</propaytypemoney>
<probenefitid>50</probenefitid>
<muserid>50</muserid>
<protype>50</protype>
<proserverce>50</proserverce>
<proisreturn>50</proisreturn>
<promark>50</promark>
</pro>
</prolist>';
WITH XMLNAMESPACES(DEFAULT 'http://cd.love.com/SOA')
SELECT T.c.value('(proid/text())[1]','INT') proid,
T.c.value('(buynumber/text())[1]','INT') buynumber,
T.c.value('(promoney/text())[1]','decimal(18, 2)') promoney,
T.c.value('(couponmoney/text())[1]','decimal(18, 2)') couponmoney,
T.c.value('(prototalamount/text())[1]','decimal(18, 2)') prototalamount,
T.c.value('(procost/text())[1]','decimal(18, 2)') procost,
T.c.value('(propaytypemoney/text())[1]','nvarchar(500)') propaytypemoney,
T.c.value('(probenefitid/text())[1]','INT') probenefitid,
T.c.value('(muserid/text())[1]','INT') muserid,
T.c.value('(protype/text())[1]','INT') protype,
T.c.value('(proserverce/text())[1]','nvarchar(200)') proserverce,
T.c.value('(proisreturn/text())[1]','INT') proisreturn,
T.c.value('(promark/text())[1]','nvarchar(500)') promark
FROM @ItemMessage.nodes('/prolist/pro') AS T(c)

0 0