如何通过 t-sql 从 URL 读取多个 xml 值 [英] how can i read multiple xml values from URL through t-sql

查看：41
本文介绍了如何通过 t-sql 从 URL 读取多个 xml 值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何在t-sql中读取xml?>

我已按照上述解决方案进行操作，但仍然无法实现..

这是我的 XML

 <结果><count>2</count><行><行><f><n>id</n><v>8557526</v></f><f><n>vdb_id</n><v>16239</v></f><f><n>创建</n><v>2014-12-10T08:50:18</v></f><f><n>task_id</n><v>5755155</v></f><f><n>process_id</n><v/></f><f><n>update_comments</n><v/></f></row><行><f><n>id</n><v>8567425</v></f><f><n>vdb_id</n><v>16239</v></f><f><n>创建</n><v>2014-12-11T00:23:59</v></f><f><n>task_id</n><v>5755155</v></f><f><n>process_id</n><v/></f><f><n>update_comments</n><v/></f></row>

查询:

 使用 tempdb去如果 OBJECT_ID('tempdb..#xml') 不是 NULL 删除表 #xml创建表 #xml ( yourXML XML )去声明@URL VARCHAR(8000)声明@QS varchar(50)SELECT @QS = '&date='+convert(varchar(25),getdate(),126)SELECT @URL = 'https://app.is.com/psa/api.do?function=query&table=db_time_entry&project_id=227666&token=c9adf' + @QS声明@Response varchar(8000)声明@XML xml声明@Obj int声明@Result int声明 @HTTPStatus int声明@ErrorMsg varchar(MAX)EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Obj OUTEXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, falseEXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'EXEC @Result = sp_OAMethod @Obj, send, NULL, ''EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT插入 #xml ( yourXML )EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT--SELECT yourXML.value('//result[1]//count[1]//rows[1]//row[1]//f[1]//n[1]//v[1]/id','VARCHAR(MAX)') 来自 #xml声明@xml_value xml从#xml中选择*从 #xml 中选择 @xml_value = yourXML

我试过这个

 选择Key1 = Item.value('(n)[1]', 'int'),Key2 = Item2.value('(n)[1]', 'int'),ItemValue = Item2.value('(v)[1]', 'varchar(50)')从@xml_value.nodes('/result/rows') AS T(Item)交叉申请item.nodes('row/f') AS T2(Item2)

我得到了这样的输出

 Key2 ItemValue编号 8557526vdb_id 16239创建 2014-12-10T08:50:18task_id 5755155process_id更新评论编号 8567425vdb_id 16239创建 2014-12-11T00:23:59task_id 5755155process_id更新评论

我需要类似的输出

ID vdb_id created task_id process_id update_comments8557526 16239 2014-12-10 5755155 null null

请任何人告诉我如何从 XML 中选择值并获得所需的输出提前致谢

问候

纳文

解决方案

对于 SQL Server，使用 PIVOT 可以得到想要的结果.

DECLARE @xmlData AS XMLSET @xmlData = CAST('<result><count>2</count><行><行><f><n>id</n><v>8557526</v></f><f><n>vdb_id</n><v>16239</v></f><f><n>创建</n><v>2014-12-10T08:50:18</v></f><f><n>task_id</n><v>5755155</v></f><f><n>process_id</n><v/></f><f><n>update_comments</n><v/></f></row><行><f><n>id</n><v>8567425</v></f><f><n>vdb_id</n><v>16239</v></f><f><n>创建</n><v>2014-12-11T00:23:59</v></f><f><n>task_id</n><v>5755155</v></f><f><n>process_id</n><v/></f><f><n>update_comments</n><v/></f></row></rows>'作为 XML)SELECT Piv.Id, piv.[vdb_id], piv.[created], piv.[task_id], piv.[update_comments]从(SELECT Result1.value('v[1]','VARCHAR(200)') AS A,Result1.value('n[1]','VARCHAR(200)') AS B,DENSE_RANK() over(order by Result) AS NumFROM @xmlData.nodes('//result/rows/row') xmlData(Result)交叉应用 xmlData.Result.nodes('./f') xmlData1(Result1)) 作为一个枢轴 (Min(A) FOR B IN ([id],[vdb_id],[创建]，[task_id],[更新评论])) piv

How to read xml in t-sql?

I have followed the above solution, but still i could not able to achieve it..

Here is my XML

 <result>
  <count>2</count>
  <rows>
<row>
  <f>
    <n>id</n>
    <v>8557526</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-10T08:50:18</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>
<row>
  <f>
    <n>id</n>
    <v>8567425</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-11T00:23:59</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>        
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>

query :

  USE tempdb
GO

  IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
  CREATE TABLE #xml ( yourXML XML )
   GO

  DECLARE @URL VARCHAR(8000) 

  DECLARE @QS varchar(50)

  SELECT @QS = '&date='+convert(varchar(25),getdate(),126)
  SELECT @URL = 'https://app.is.com/psa/api.do?function=query&table=db_time_entry&     project_id=227666&token=c9adf' + @QS


   DECLARE @Response varchar(8000)
   DECLARE @XML xml
   DECLARE @Obj int 
   DECLARE @Result int 
   DECLARE @HTTPStatus int 
   DECLARE @ErrorMsg varchar(MAX)


   EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Obj OUT 

   EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
   EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
   EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
    EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 

    INSERT #xml ( yourXML )
    EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 

     --SELECT  yourXML.value('//result[1]//count[1]//rows[1]//row[1]//f[1]//n[1]//v[1]  /id','VARCHAR(MAX)') from #xml

    DECLARE @xml_value xml

     select * from #xml
     select @xml_value = yourXML  from #xml

I tried this one 

 SELECT
  Key1 = Item.value('(n)[1]', 'int'),
  Key2 = Item2.value('(n)[1]', 'int'),
   ItemValue = Item2.value('(v)[1]', 'varchar(50)')
 FROM 
   @xml_value.nodes('/result/rows') AS T(Item)
 CROSS APPLY
item.nodes('row/f') AS T2(Item2)

i got the out put like this

    Key2    ItemValue
     id      8557526
     vdb_id   16239
     created    2014-12-10T08:50:18
     task_id    5755155
    process_id  
    update_comments 
      id    8567425
       vdb_id   16239
      created   2014-12-11T00:23:59
      task_id   5755155
     process_id 
       update_comments

I need Output Like

ID    vdb_id   created   task_id   process_id    update_comments

 8557526   16239  2014-12-10  5755155  null   null

please anyone tell me how I could select values from the XML and get the desired output Thanks in advance

Regards

T.Navin

解决方案

For SQL Server, Use PIVOT to get the desired result . 

DECLARE @xmlData AS XML


  SET @xmlData = CAST('<result>
  <count>2</count>
  <rows>
<row>
  <f>
    <n>id</n>
    <v>8557526</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-10T08:50:18</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>
<row>
  <f>
    <n>id</n>
    <v>8567425</v>
  </f>
  <f>
    <n>vdb_id</n>
    <v>16239</v>
  </f>
  <f>
    <n>created</n>
    <v>2014-12-11T00:23:59</v>
  </f>
  <f>
    <n>task_id</n>
    <v>5755155</v>
  </f>
  <f>
    <n>process_id</n>
    <v />
  </f>        
  <f>
    <n>update_comments</n>
    <v />
  </f>
</row>
</rows>
</result>' AS XML)

SELECT Piv.Id, piv.[vdb_id], piv.[created], piv.[task_id], piv.[update_comments] 
FROM 
(
    SELECT Result1.value('v[1]','VARCHAR(200)') AS A, 
           Result1.value('n[1]','VARCHAR(200)') AS B,
         DENSE_RANK() over(order by Result) AS Num
    FROM @xmlData.nodes('//result/rows/row') xmlData(Result) 
    CROSS APPLY xmlData.Result.nodes('./f') xmlData1(Result1)
) AS A    
Pivot (Min(A) FOR B IN ([id],
                        [vdb_id],
                        [created],
                        [task_id],
                        [update_comments])
       ) piv

这篇关于如何通过 t-sql 从 URL 读取多个 xml 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆