解析XML样例集

最新推荐文章于 2024-04-18 20:24:08 发布

otong

最新推荐文章于 2024-04-18 20:24:08 发布

阅读量451

点赞数

分类专栏： SQL Server 文章标签： xml c

本文链接：https://blog.csdn.net/otong/article/details/6610280

版权

SQL Server 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

请看示例：

declare @xml xml
select @xml = '<public xmlns="http://angelia.xml.test">
<employee>
<name>angelia Ou</name>
<age>22</age>
</employee>
<employee>
<name>Mary Li</name>
<age>26</age>
</employee>
</public>'

--解析成一个数据集

;with xmlnamespaces( default 'http://angelia.xml.test' ) --如果前面public结点没有指明命名空间，则这一句可以去掉。
SELECT
T.c.value('(./name/text())[1]','varchar(25)') as name
,T.c.value('(./age/text())[1]','int') as age
FROM @XML.nodes('/public/employee') AS T(c)

--解析到一个变量中
declare @Name1 varchar(12),@Name2 varchar(12)

;with xmlnamespaces( default 'http://angelia.xml.test' )
SELECT @Name1 = @xml.value('(/public/employee/name/text())[1]',N'varchar(12)')
,@Name2 = @xml.value('(/public/employee/name/text())[2]',N'varchar(12)')

select @Name1,@Name2

--解析一组XML值

declare @FromMessagexml
 
select @FromMessage = '<Publish xmlns="http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService"> 
  <Node>
    <Body>
      <Request>
        <MerchantFieldSet>
         <MerchantFieldName>name1</MerchantFieldName>
         <MerchantFieldName>
           name2
         </MerchantFieldName>
         <MerchantFieldName>
           name3
         </MerchantFieldName>
         <MerchantFieldName>
           <![CDATA[name&&&(((---///887^^%%$$##74545]]>
         </MerchantFieldName>
       </MerchantFieldSet>       
      </Request>
    </Body>
  </Node>
</Publish>'
 
;WITH XMLNAMESPACES (DEFAULT 'http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService')
SELECT
    MerchantFieldSet.value('(./text())[1]','varchar(30)') AS MerchantFieldName   
FROM @FromMessage.nodes('/Publish/Node/Body/Request/MerchantFieldSet/MerchantFieldName') BV(MerchantFieldSet)

---解析多组结点中表示的是主从表数据的XML

declare @FromMessage xml

select @FromMessage = '<Publish xmlns="http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService">  
  <Node>
    <Body>
      <Request>
        <MerchantFieldSet>
          <id>111</id>
          <childset>
                    <MerchantFieldName1>name1</MerchantFieldName1>
                    <MerchantFieldName2>name2</MerchantFieldName2>
          </childset>
          <childset>
                    <MerchantFieldName1>name3</MerchantFieldName1>
                    <MerchantFieldName2>name4</MerchantFieldName2>
          </childset>
        </MerchantFieldSet>
            <MerchantFieldSet>
                    <id>222</id>
                    <childset>
                          <MerchantFieldName1>name11</MerchantFieldName1>
                          <MerchantFieldName2>name22</MerchantFieldName2>
                    </childset>
                    <childset>
                          <MerchantFieldName1>name33</MerchantFieldName1>
                          <MerchantFieldName2>name44</MerchantFieldName2>
                    </childset>
                  </MerchantFieldSet>
           <MerchantFieldSet>
                    <id>333</id>
                    <childset>
                          <MerchantFieldName1>name111</MerchantFieldName1>
                          <MerchantFieldName2>name222</MerchantFieldName2>
                    </childset>
                    <childset>
                          <MerchantFieldName1>name333</MerchantFieldName1>
                          <MerchantFieldName2>name444</MerchantFieldName2>
                    </childset>
                  </MerchantFieldSet>
      </Request>
    </Body>
  </Node>
</Publish>'

--;WITH XMLNAMESPACES (DEFAULT 'http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService')
--SELECT
--     BV.c.value('(id/text())[1]','int')
--    ,T.C.value('(MerchantFieldName1/text())[1]','varchar(30)') AS MerchantFieldName1    
--    ,T.C.value('(MerchantFieldName2/text())[1]','varchar(30)') AS MerchantFieldName2
--FROM @FromMessage.nodes('/Publish/Node/Body/Request/MerchantFieldSet') BV(c)
--CROSS APPLY BV.c.nodes(N'childset') T(C)


;WITH XMLNAMESPACES (DEFAULT 'http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService')
SELECT
     T.c.value('(../id/text())[1]','int')
    ,T.C.value('(MerchantFieldName1/text())[1]','varchar(30)') AS MerchantFieldName1    
    ,T.C.value('(MerchantFieldName2/text())[1]','varchar(30)') AS MerchantFieldName2
FROM @FromMessage.nodes('/Publish/Node/Body/Request/MerchantFieldSet/childset') T(C)

otong

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
解析XML样例集

请看示例：declare @xml xmlselect @xml = 'angelia Ou22Mary Li26'--解析成一个数据集;with xmlnamespaces( default 'http://angel
复制链接

扫一扫