Using the VSICodeService Web Service

Ove​rview

Since VSI began to sup­port mul­ti­ple SIF ver­sions with its prod­ucts, we have had an issue with prop­er­ly man­ag­ing the many dif­fer­ent sets of data­base def­i­n­i­tions that have been required by each of the dif­fer­ent SIF ver­sions. As of the writ­ing of this arti­cle, there are 22 dis­tinct ver­sions of the SIF spec­i­fi­ca­tion that have been pub­lished across the three regions of the world.

VSICentralServ​ice — Public Information

The VSI­Cen­tralSer­vice web ser­vice is an oDa­ta web ser­vice that returns use­ful infor­ma­tion about the SIF spec­i­fi­ca­tion in elec­tron­ic form that can be con­sumed through pro­gram­ming libraries, data­bas­es or by appli­ca­tions such as Excel, Pow­er­Piv­ot or CMS sys­tems. Each record is time-stamped so that agents and appli­ca­tions can be kept up-to-date with­out need­ing to con­tin­u­al­ly reload the entire data­base.

This data­base con­tains some infor­ma­tion already avail­able in elec­tron­ic form but most­ly con­tains that which is main­tained by Visu­al Soft­ware staff. This is because much of the infor­ma­tion pub­lished in the spec­i­fi­ca­tion is not pub­lished in tab­u­lar form — it is either pub­lished as XSD files, PDFs or Excel files meant for peo­ple to read and are not eas­i­ly con­vert­ible for use by soft­ware appli­ca­tions.

We have found that when­ev­er we make assump­tions in our envi­ron­ment that any­thing is "fixed" in nature, it changes. Because of this dynam­ic nature, we have cre­at­ed sev­er­al dif­fer­ent ser­vices, one each pro­vid­ing infor­ma­tion about:

  • The SIF Spec­i­fi­ca­tion — Pub­lic
    • Coun­tries (regions)
    • Local­i­ties — places that define local exten­sions
    • Local­i­ty Ref­er­ences — spec­i­fi­ca­tions man­aged at a local­i­ty
    • Spec­i­fi­ca­tion ver­sions (com­bi­na­tions of data mod­el and infra­struc­ture ver­sions)
    • SIF Objects
    • SIF Enti­ties — these are all the ele­ments and attrib­ut­es defined in the spec­i­fi­ca­tion
    • SIF Code Val­ues
    • SIF Extend­ed Ele­ments

The VSI­Cen­tral data­base con­tains infor­ma­tion about all ver­sions of the SIF spec­i­fi­ca­tion (as of Jan 2014, there are 16 ver­sions in the data­base and over a mil­lion dif­fer­ent code val­ues). In order to pre­vent a sin­gle con­sumer from monop­o­liz­ing all the resources of the sin­gle shared serv­er, the web ser­vice itself lim­its return val­ues from queries to 20,000 items.

So, when mak­ing requests from this web ser­vice, it is rec­om­mend­ed that you fil­ter your requests to lim­it the infor­ma­tion to only that which you need. This can be done when call­ing the web ser­vice using the oDa­ta URL con­ven­tions defined in oDa­ta URI Con­ven­tions. Good exam­ples can also be found at: Fil­ter Exam­ples.

SIF Code​Values

This repos­i­to­ry con­tains the stan­dard SIF code val­ues as well as those local­ly defined by states and oth­er local­i­ties.

SIF Code Values Query —​Example

In the fol­low­ing exam­ple, we use the fol­low­ing URL to call the web ser­vice, ask­ing it to return infor­ma­tion about code val­ues used in the SIF Object 'Stu­dentSec­tio­nEn­roll­ment' for the Data­Mod­elVer­sion '2.0R1'. Since only the US has a data mod­el with that ver­sion num­ber, no oth­er coun­tries will rep­re­sent­ed in the returned infor­ma­tion. The URL will be:

http://www.sifdepot.com/VSICentralService.svc/sifCodeValues?$filter=DataModelVersion eq '2.0r1' and Object­Name eq 'Stu­dentSec­tio­nEn­roll­ment'

Once exe­cut­ed, the returned infor­ma­tion will look like this in a brows­er win­dow:

SSE%20Codes

(note: some browsers do not (or no longer) dis­play XML mes­sages prop­er­ly or need a plug-in before they are able to dis­play for­mat­ted XML.

Information Stored for Each Co​de Value

The fol­low­ing is the def­i­n­i­tion of the table in the sup­port­ing data­base that con­tains the infor­ma­tion returned about each returned code val­ue.

Key Name Data Type Max Length (Bytes) Allow Nulls Iden­ti­ty Default
Key.png Recor­dID Unique iden­ti­fi­er in the code val­ues table int 4 False 1 — 1
Node-tree.png(4) Coun­tryCode Data Mod­el Coun­try — US, UK, AU or GLOBAL varchar(10) 10 True
Node-tree.png(4) Data­Mod­elVer­sion Data Mod­el Ver­sion varchar(10) 10 True
Node-tree.png(4) Infra­struc­ture­Ver­sion Infra­struc­ture that sup­ports this data mod­el for this coun­try (use­ful for queries) varchar(10) 10 True
Node-tree.png(3) Local­i­ty­Name If exten­sion, the name of the local­i­ty main­tain­ing this code val­ue varchar(50) 50 True
Local­i­tyRef­er­ence If this is a local code val­ue, this refers to the local spec­i­fi­ca­tion that pro­vides more detail for this code varchar(75) 75 True
Node-tree.png(2) Object­Name SIF object relat­ed to the element/attribute that uses this code val­ue varchar(255) 255 True
Item­Type E=Element, A=Attribute varchar(5) 5 True
Node-tree.png Item­Name Base ele­ment name asso­ci­at­ed with this code val­ue (even if the code val­ue is direct­ly asso­ci­at­ed with an attribute) — this is use­ful for search­ing varchar(75) 75 True
SIFI­den­ti­fi­er - this is a hexa­dec­i­mal val­ue assigned to this code val­ue to make these val­ues com­pat­i­ble with the SIF 3.* spec­i­fi­ca­tions. varchar(50) 50 True
Node-tree.png XPATH XPATH of item asso­ci­at­ed with this code val­ue (ele­ment or attribute) varchar(255) 255 True
Code­Val­ue The code val­ue as defined in the spec­i­fi­ca­tion (orig­i­nat­ed by an out­side author­i­ty such as the NCES) varchar(255) 255 True
Code­Mean­ing
Descrip­tion — use­ful in appli­ca­tions for lookup tables
varchar(max) max True
Cod­eDescrip­tion varchar(max) max True
Date­Cre­at­ed
Date this entry was first cre­at­ed
date­time 8 True (get­date())
Las­tUp­dat­ed
Date this entry was last edit­ed
date­time 8 True (get­date())
IsDep­re­cat­ed
This code val­ue will be removed in a future release of the SIF spec­i­fi­ca­tion
bit 1 True ((0))
Las­tAc­tion
This may either be 'Add" or 'Change"
varchar(10) 10 True ('Add')
Cre­at­ed­By
Name of the account that cre­at­ed this code val­ue table entry
varchar(255) 255 True ('admin')
Main­tained­By Name of the account that has author­i­ty to main­tain this table entry — this has sig­nif­i­cance when the code val­ue is local­ly main­tained; it will cor­re­spond to an account name of the local­i­ty-assigned user respon­si­ble for main­tain­ing these entries varchar(255) 255 True

The XML returned by the web ser­vice has a 1–1 cor­re­spon­dence to the val­ues in this table.

SIF Ent​ities — Obje​ct Detail

This method returns infor­ma­tion from the SIF spec­i­fi­ca­tion. We have found it help­ful for pop­u­lat­ing drop­down box­es and keep­ing oth­er parts of the SIF agent up to date.

SIF Entities Query​— Exam​ple

In the fol­low­ing exam­ple, we use the fol­low­ing URL to call the web ser­vice, ask­ing it to return infor­ma­tion about the SIF Object 'Stu­dentSec­tio­nEn­roll­ment' for the Data­Mod­elVer­sion '2.0R1'. Since only the US has a data mod­el with that ver­sion num­ber, no oth­er coun­tries will rep­re­sent­ed in the returned infor­ma­tion. The URL will be:

http://www.sifdepot.com/VSICentralService.svc/sifEn­ti­ties?$filter=DataModelVersion eq'2.0r1' and Object­Nameeq'Stu­dentSec­tio­nEn­roll­ment'

Once exe­cut­ed, the returned infor­ma­tion will look like this in a brows­er win­dow:

(note: some browsers do not (or no longer) dis­play XML mes­sages prop­er­ly or need a plug-in before they are able to dis­play for­mat­ted XML.

Countries (re​gions)

This table con­tains infor­ma­tion about the regions (coun­tries) for which infor­ma­tion is main­tained by this ser­vice. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes) Descrip­tion
Recor­dID int 4
Coun­tryCode varchar(10) 10 The short code as most com­mon­ly used to ref­er­ence the coun­try
Coun­try­Name varchar(50) 50 The full name of the coun­try or region
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF Coun​tries​— Exam​ple

Countries

SIF Versions (​combinatio​ns of data model and infrastructure versions)

This method returns infor­ma­tion about the com­bi­na­tions of Coun­try /Infrastructure / Data Mod­el ver­sions for which infor­ma­tion is avail­able from this ser­vice. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes)
Recor­dID int 4
Coun­tryCode varchar(10) 10
Infra­struc­ture­Ver­sion varchar(50) 50
Data­Mod­elVer­sion varchar(50) 50
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF Versions​— E​xam​ple

Localities — places th​at​define local extensions

This method returns infor­ma­tion about the local­i­ties about which Extend­ed Ele­ments and Code Val­ues are main­tained. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes) Descrip­tion
Recor­dID int 4
Local­i­ty­Name varchar(50) 50 Code used as a ref­er­ence in oth­er tables
Local­i­ty­De­scrip­tion varchar(255) 255 A full descrip­tion of the local­i­ty
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF Localities — Exa​m​ple

Locality References — s​pe​cifications managed at a locality

This method returns detailed infor­ma­tion about the spec­i­fi­ca­tions referred to in the Local­i­ty Def­i­n­i­tions.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes) Descrip­tion
Recor­dID int 4
Local­i­ty­Name varchar(50) 50 state (or oth­er local­i­ty) name
Local­i­tyRef­er­ence varchar(50) 50 the name of the spec­i­fi­ca­tion from which this val­ue was tak­en
(abbre­vi­a­tion)
sif­Code­setId varchar(50) 50 hex unique val­ue for this code­set
Descrip­tion varchar(254) 254 the name of the spec­i­fi­ca­tion from which this val­ue was tak­en (full)
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF Locality References​—​Exam​ple

SI​F Objec​ts

This method returns infor­ma­tion about the objects that are avail­able with each ver­sion of the SIF spec­i­fi­ca­tion. Remem­ber to apply fil­ters for the Coun­tryCode and spec­i­fi­ca­tion ver­sions when call­ing this method.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes) Descrip­tion
Recor­dID int 4
Coun­tryCode varchar(10) 10
Data­Mod­elVer­sion varchar(10) 10
Infra­struc­ture­Ver­sion varchar(10) 10
Local­i­ty­Name varchar(50) 50 This would be only used if an entire cus­tom object were being cre­at­ed.
Object­Name varchar(175) 175
Work­ing­Group varchar(175) 175 This is option­al (some regions do not assign objects to work­ing groups)
EventsRe­port­ed bit 1 A flag indi­cat­ing that this is an object where events are gen­er­at­ed. All objects sup­port requests and respons­es.
Descrip­tion varchar(max) max Full descrip­tion of the object
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF​Objects​— Exam​ple

SIF Extended El​emen​ts

Call­ing this method will return infor­ma­tion about Extend­ed Ele­ments reg­is­tered with Visu­al Soft­ware for the Local­i­ty spec­i­fied.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

Name Data Type Max Length (Bytes) Descrip­tion
Recor­dID int 4
Coun­tryCode varchar(10) 10 Coun­try or region where defined
Data­Mod­elVer­sion varchar(10) 10
Infra­struc­ture­Ver­sion varchar(10) 10
Local­i­ty­Name varchar(50) 50 Where this EE is being used
Local­i­tyRef­er­ence varchar(50) 50 The name of the local spec­i­fi­ca­tion that gives more detail about how this EE works
Object­Name varchar(175) 175 SIF object where this EE is attached
Extend­edEle­ment­Name varchar(255) 255 Name of EE as will be used in the XML mes­sage
Extend­edEle­ment­Type varchar(max) max O (Option­al), M (Manda­to­ry, C (Con­di­tion­al)
Descrip­tion varchar(max) max Full descrip­tion of the EE — this will not be trans­mit­ted with the data
Date­Cre­at­ed date­time 8
Las­tUp­dat­ed date­time 8
IsDep­re­cat­ed bit 1
Las­tAc­tion varchar(10) 10
Cre­at­ed­By varchar(255) 255
Main­tained­By varchar(255) 255

SIF Extended Elements — Exam​ple​


vsiCentralService — oData Information Ac​cess

The for­mat of the XML returned by this web ser­vice is defined by the oDa­ta spec­i­fi­ca­tion. Accord­ing to the www.odata.org web site, the Open Data Pro­to­col is defined as fol­lows:

ODa­ta is a stan­dard­ized pro­to­col for cre­at­ing and con­sum­ing data APIs. ODa­ta builds on core pro­to­cols like HTTP and com­mon­ly accept­ed method­olo­gies like REST. The result is a uni­form way to expose full-fea­tured data APIs.

By mak­ing use of the oDa­ta out­put for­mat, the out­put of this web ser­vice can be used as input in many ways.

  • There are many freely avail­able libraries avail­able that accept oDa­ta input sources, includ­ing
    • .NET
    • Javascript
    • Java
    • PHP
    • Ruby
    • C++
    • Sil­verlight
    • Telerik envi­ron­ments
  • There are CMS and oth­er appli­ca­tions that accept oDa­ta input, includ­ing:
    • Joom­la
    • Dru­pal
    • Web­Sphere
    • Pow­er­Piv­ot, Excel
    • Dynam­ics
    • SAP
    • Webn­odes
    • eBay
    • Nuget
  • Data­bas­es
    • SQL Serv­er
    • MySQL
    • Azure, oth­ers

As con­fir­ma­tion that this ser­vice has passed all the require­ments for an oDa­ta end­point, we ran the code ser­vice through the oDa­ta val­i­da­tion tool (the clos­est thing they have to cer­ti­fi­ca­tion):​

800px-SpecServiceValidated.PNG

..and it passed all the valid­i­ty tests.

Consuming Web Service​Data

Through the ODa­ta inter­face, the data from the VS SIF Infor­ma­tion Web Ser­vice may be con­sumed by a host of oth­er inter­faces. For an up to date list of providers, con­sumers, live ser­vices and pro­gram­ming libraries that sup­port ODa­ta (and, hence this web ser­vice), see: ODa­ta Ecosys­tem

Importing​Into Excel

As an exam­ple, this series of screen shots illus­trate how this web ser­vice data may be import­ed into an Excel spread­sheet. First, on Excel's "Data" tab, choose to import data from the web. This fea­ture has been avail­able (at least) from Office 2010. (Click image to see full view).

600px-Excel001.PNG
Enter the URL of the web ser­vice, includ­ing fil­ters into the "Address" input box and click the "Go" but­ton. If the URL you spec­i­fied will return many rows of data, expect to wait a few min­utes.

600px-Excel002.PNG
Excel will dis­play the out­put of the web ser­vice in its XML form in the win­dow. Click the "right arrow" but­ton shown here in this pic­ture to select the entire con­tents.​

600px-Excel003.PNG

When the XML has been select­ed, Press the 'Import' but­ton. Depend­ing on how much data has been loaded from the web ser­vice, this may take a few min­utes.​

600px-Excel004.PNG

This screen will ask you if you want to add this data to the cur­rent work­sheet or to a new work­sheet.

Excel005.PNG
This is a typ­i­cal error — since this web ser­vice will always out­put some numer­ic columns and since Excel will always trans­late them to text columns, this is warn­ing you that it is doing so. If you want to use them as numer­ic columns in Excel, you will need to man­u­al­ly change them back.Excel006.PNG Your data has been import­ed. Notice that ALL of the web ser­vice out­put has been import­ed, includ­ing some columns that you will not need in Excel. If you are going to use this same ODa­ta out­put for input to pro­gram­ming con­trols, how­ev­er, they will need this infor­ma­tion, so we need to dis­play it. If you don't want to see it, man­u­al­ly remove the columns you don't need.​

600px-Excel007.PNG

Do NOT follow this link or you will be banned from the site!