how can i read multiple xml values from URL through t-sql

Question

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

enter image description here

Saravana Kumar · Accepted Answer · 2014-12-15 13:28:50Z

2

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

edited Dec 15, 2014 at 13:28

answered Dec 12, 2014 at 7:08

Saravana Kumar

3,7275 gold badges19 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

navbingo Over a year ago

if i dont want to store the process_id column in my table , what should i do?

Saravana Kumar Over a year ago

@navbingo: Remove the [process_id] in PIVOT column list. I updated the result, verify it.

navbingo Over a year ago

ok thanks... but i am facing another problem.. it is displaying only one row ;(

navbingo Over a year ago

i have edited my question.. i have added an image.. i am running two queries.. the second one is the pivot one (ur's).. the first query showing all the 8 records (total 32 xml columns * 8 =256).. where as the pivot one shows only one record..

Saravana Kumar Over a year ago

@navbingo: I have updated the result. verify it and let me know the status.

|

Collectives™ on Stack Overflow

how can i read multiple xml values from URL through t-sql

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related