Setting Up Job to Read XML into SQL Tables

• cajun_sql

  SSCommitted

  Points: 1842

  More actions

  July 21, 2016 at 9:56 am

  #309419

  Hi,

  I've been wanting to set up a job which will schedule a Stored Procedure, which will read XML from a RESTful service into selected SQL Server tables. Over the past few months, I've researched this, but I've been unsure about where/how to start.

  Initially, I experimented with Visual Studio, but I became aware that the difference between the version I have (2012 Community) and the one in the tutorial (from 2009, here) was just enough to cloud things up a bit. http://www.databasejournal.com/features/mssql/article.php/3821271/Calling-a-Web-Service-from-within-SQL-Server.htm 🙂

  Would there be a 'layout' that would show me where to start and how to proceed? For example, would I be creating code in Visual Studio to create a Stored Procedure, or would I need to focus upon CLR, etc?

  Here is my URL for the RESTful service.

  http://www.digitalmeasures.com/login/service/v4/User/INDIVIDUAL-ACTIVITIES-Pharmacy

  Would a URL necessarily need to end in '.xml' in order to work with this (or be in some certain format)? (I've also copied a section of the XML which you would see after the authentication process [it is password protected, currently, so I hope this is workable.])

  Thanks in advance for any ideas. 🙂

  <?xml version="1.0" encoding="UTF-8"?>

  <Data xmlns="http://www.digitalmeasures.com/schema/data" xmlns:dmd="http://www.digitalmeasures.com/schema/data-metadata" dmd:date="2016-07-21">

  <Record userId="1480926" username="bob-tocows" termId="4402" dmd:surveyId="12020699">

  <dmd:IndexEntry indexKey="DEPARTMENT" entryKey="Studies" text="Studies"/>

  <INTELLPROP id="11" dmd:lastModified="2015-02-23T12:01:35" dmd:startDate="2014-01-01" dmd:endDate="2014-12-31">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern A</TITLE>

  <ID_NUMBER>22</ID_NUMBER>

  <TYPE/>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="87992387585">

  <FACULTY_NAME>1480926</FACULTY_NAME>

  <FNAME>Bob</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED>Study Group</WHOM_ASSIGNED>

  <DTM_SUBMIT/>

  <DTD_SUBMIT/>

  <DTY_SUBMIT/>

  <SUBMIT_START></SUBMIT_START>

  <SUBMIT_END></SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION>2014</DTY_APPLICATION>

  <APPLICATION_START>2014-01-01</APPLICATION_START>

  <APPLICATION_END>2014-12-31</APPLICATION_END>

  <DTM_APPROVE/>

  <DTD_APPROVE/>

  <DTY_APPROVE/>

  <APPROVE_START></APPROVE_START>

  <APPROVE_END></APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  <INTELLPROP id="12" dmd:lastModified="2016-02-26T11:04:44" dmd:startDate="2009-01-01" dmd:endDate="2014-01-31">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern A</TITLE>

  <ID_NUMBER>23</ID_NUMBER>

  <TYPE>Regular</TYPE>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="87992010753">

  <FACULTY_NAME>1480926</FACULTY_NAME>

  <FNAME>Bob</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED>Study Group</WHOM_ASSIGNED>

  <DTM_SUBMIT>January</DTM_SUBMIT>

  <DTD_SUBMIT/>

  <DTY_SUBMIT>2009</DTY_SUBMIT>

  <SUBMIT_START>2009-01-01</SUBMIT_START>

  <SUBMIT_END>2009-01-31</SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION/>

  <APPLICATION_START></APPLICATION_START>

  <APPLICATION_END></APPLICATION_END>

  <DTM_APPROVE>January</DTM_APPROVE>

  <DTD_APPROVE/>

  <DTY_APPROVE>2014</DTY_APPROVE>

  <APPROVE_START>2014-01-01</APPROVE_START>

  <APPROVE_END>2014-01-31</APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  </Record>

  <Record userId="1480927" username="sandy-tocows" termId="4402" dmd:surveyId="12020700">

  <dmd:IndexEntry indexKey="DEPARTMENT" entryKey="Studies" text="Studies"/>

  <INTELLPROP id="88173131776" dmd:lastModified="2016-02-25T09:08:32" dmd:startDate="2015-11-03" dmd:endDate="2015-11-03">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern 2C</TITLE>

  <ID_NUMBER>43</ID_NUMBER>

  <TYPE>Regular</TYPE>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="88173131777">

  <FACULTY_NAME>1480927</FACULTY_NAME>

  <FNAME>Sandy</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <INTELLPROP_INVENT id="88173131778">

  <FACULTY_NAME/>

  <FNAME>Bob</FNAME>

  <MNAME/>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED/>

  <DTM_SUBMIT/>

  <DTD_SUBMIT/>

  <DTY_SUBMIT/>

  <SUBMIT_START></SUBMIT_START>

  <SUBMIT_END></SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION/>

  <APPLICATION_START></APPLICATION_START>

  <APPLICATION_END></APPLICATION_END>

  <DTM_APPROVE>November</DTM_APPROVE>

  <DTD_APPROVE>03</DTD_APPROVE>

  <DTY_APPROVE>2015</DTY_APPROVE>

  <APPROVE_START>2015-11-03</APPROVE_START>

  <APPROVE_END>2015-11-03</APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  </Record>

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 21, 2016 at 11:00 pm

  #1891490

  This shouldn't be too hard, even calling the web service from SQL Server is straight forward, here is an example function from this thread

  😎

  CREATE function [dbo].[GetHttp]

  (

  @url varchar(8000)

  )

  returns varchar(8000)

  WITH EXECUTE AS OWNER

  as

  BEGIN

  DECLARE @win int

  DECLARE @hr int

  DECLARE @text varchar(8000)

  EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAMethod @win,'Send'

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OADestroy @win

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  RETURN @text

  END

  GO

  Once you got the content, parsing it into tables is easy, here is a quick example based on the XML snipped you posted, this can easily be expanded to produce multiple sets and include all the data from the message.

  USE TEEST;

  GO

  SET NOCOUNT ON;

  DECLARE @TXML XML = '<?xml version="1.0" encoding="UTF-8"?>

  <Data xmlns="http://www.digitalmeasures.com/schema/data" xmlns:dmd="http://www.digitalmeasures.com/schema/data-metadata" dmd:date="2016-07-21">

  <Record userId="1480926" username="bob-tocows" termId="4402" dmd:surveyId="12020699">

  <dmd:IndexEntry indexKey="DEPARTMENT" entryKey="Studies" text="Studies"/>

  <INTELLPROP id="11" dmd:lastModified="2015-02-23T12:01:35" dmd:startDate="2014-01-01" dmd:endDate="2014-12-31">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern A</TITLE>

  <ID_NUMBER>22</ID_NUMBER>

  <TYPE/>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="87992387585">

  <FACULTY_NAME>1480926</FACULTY_NAME>

  <FNAME>Bob</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED>Study Group</WHOM_ASSIGNED>

  <DTM_SUBMIT/>

  <DTD_SUBMIT/>

  <DTY_SUBMIT/>

  <SUBMIT_START></SUBMIT_START>

  <SUBMIT_END></SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION>2014</DTY_APPLICATION>

  <APPLICATION_START>2014-01-01</APPLICATION_START>

  <APPLICATION_END>2014-12-31</APPLICATION_END>

  <DTM_APPROVE/>

  <DTD_APPROVE/>

  <DTY_APPROVE/>

  <APPROVE_START></APPROVE_START>

  <APPROVE_END></APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  <INTELLPROP id="12" dmd:lastModified="2016-02-26T11:04:44" dmd:startDate="2009-01-01" dmd:endDate="2014-01-31">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern A</TITLE>

  <ID_NUMBER>23</ID_NUMBER>

  <TYPE>Regular</TYPE>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="87992010753">

  <FACULTY_NAME>1480926</FACULTY_NAME>

  <FNAME>Bob</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED>Study Group</WHOM_ASSIGNED>

  <DTM_SUBMIT>January</DTM_SUBMIT>

  <DTD_SUBMIT/>

  <DTY_SUBMIT>2009</DTY_SUBMIT>

  <SUBMIT_START>2009-01-01</SUBMIT_START>

  <SUBMIT_END>2009-01-31</SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION/>

  <APPLICATION_START></APPLICATION_START>

  <APPLICATION_END></APPLICATION_END>

  <DTM_APPROVE>January</DTM_APPROVE>

  <DTD_APPROVE/>

  <DTY_APPROVE>2014</DTY_APPROVE>

  <APPROVE_START>2014-01-01</APPROVE_START>

  <APPROVE_END>2014-01-31</APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  </Record>

  <Record userId="1480927" username="sandy-tocows" termId="4402" dmd:surveyId="12020700">

  <dmd:IndexEntry indexKey="DEPARTMENT" entryKey="Studies" text="Studies"/>

  <INTELLPROP id="88173131776" dmd:lastModified="2016-02-25T09:08:32" dmd:startDate="2015-11-03" dmd:endDate="2015-11-03">

  <FORMAT>Study</FORMAT>

  <TITLE>Study Pattern 2C</TITLE>

  <ID_NUMBER>43</ID_NUMBER>

  <TYPE>Regular</TYPE>

  <NATIONALITY>United States</NATIONALITY>

  <NATIONS/>

  <INTELLPROP_INVENT id="88173131777">

  <FACULTY_NAME>1480927</FACULTY_NAME>

  <FNAME>Sandy</FNAME>

  <MNAME></MNAME>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <INTELLPROP_INVENT id="88173131778">

  <FACULTY_NAME/>

  <FNAME>Bob</FNAME>

  <MNAME/>

  <LNAME>Tocows</LNAME>

  </INTELLPROP_INVENT>

  <WHOM_LICENSED/>

  <WHOM_ASSIGNED/>

  <DTM_SUBMIT/>

  <DTD_SUBMIT/>

  <DTY_SUBMIT/>

  <SUBMIT_START></SUBMIT_START>

  <SUBMIT_END></SUBMIT_END>

  <DTM_APPLICATION/>

  <DTD_APPLICATION/>

  <DTY_APPLICATION/>

  <APPLICATION_START></APPLICATION_START>

  <APPLICATION_END></APPLICATION_END>

  <DTM_APPROVE>November</DTM_APPROVE>

  <DTD_APPROVE>03</DTD_APPROVE>

  <DTY_APPROVE>2015</DTY_APPROVE>

  <APPROVE_START>2015-11-03</APPROVE_START>

  <APPROVE_END>2015-11-03</APPROVE_END>

  <DTM_LICENSE/>

  <DTD_LICENSE/>

  <DTY_LICENSE/>

  <LICENSE_START></LICENSE_START>

  <LICENSE_END></LICENSE_END>

  <DTM_RENEWAL/>

  <DTD_RENEWAL/>

  <DTY_RENEWAL/>

  <RENEWAL_START></RENEWAL_START>

  <RENEWAL_END></RENEWAL_END>

  <USER_REFERENCE_CREATOR>Yes</USER_REFERENCE_CREATOR>

  </INTELLPROP>

  </Record>

  </Data>';

  ;WITH XMLNAMESPACES ('http://www.digitalmeasures.com/schema/data-metadata' AS XD

  ,DEFAULT 'http://www.digitalmeasures.com/schema/data')

  SELECT

  RECORD.DATA.value('@userId','INT') AS userId

  ,RECORD.DATA.value('@username','NVARCHAR(100)') AS username

  ,RECORD.DATA.value('@termId','INT') AS termId

  ,RECORD.DATA.value('@XD:surveyId','INT') AS surveyId

  ,INDEXENTRY.DATA.value('@indexKey','NVARCHAR(100)') AS indexKey

  FROM @TXML.nodes('Data/Record') RECORD(DATA)

  CROSS APPLY RECORD.DATA.nodes('XD:IndexEntry') INDEXENTRY(DATA);

  Output

  userId username termId surveyId indexKey

  ----------- ---------------- ----------- ----------- -----------

  1480926 bob-tocows 4402 12020699 DEPARTMENT

  1480927 sandy-tocows 4402 12020700 DEPARTMENT

• cajun_sql

  SSCommitted

  Points: 1842

  More actions

  July 22, 2016 at 12:48 pm

  #1891658

  Thanks very much!

  I've been also looking at the example in the link you referenced. I just left the @urlKEY value empty, as I wasn't sure where its value came from - but I got the following error, after that:

  The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

  What would be the best approach for granting permissions with this scenario?

  Thank you.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  July 22, 2016 at 1:52 pm

  #1891669

  Simply enable Ole Automation Procedures

  😎

  --SHOW ADVANCED OPTIONS

  EXEC sp_configure 'show advanced options',1;

  RECONFIGURE WITH OVERRIDE;

  -- ENABLE OLE AUTOMATION PROCS

  EXEC sp_configure 'Ole Automation Procedures',1;

  RECONFIGURE WITH OVERRIDE;

  -- HIDE ADVANCED OPTIONS

  EXEC sp_configure 'show advanced options',0;

  RECONFIGURE WITH OVERRIDE;