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:

(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
	Recor­dID Unique iden­ti­fi­er in the code val­ues table	int	4	False	1 — 1
(4)	Coun­tryCode Data Mod­el Coun­try — US, UK, AU or GLOBAL	varchar(10)	10	True
(4)	Data­Mod­elVer­sion Data Mod­el Ver­sion	varchar(10)	10	True
(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
(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
(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
	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
	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

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):​

..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).

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.

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.​

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.​

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

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. 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.​