XML - information

1. Using an XML file as a database with the TwinCAT 3 Database Server

2. Apply XPath queries to an XML file with the TwinCAT 3 Database Server

Further information about XML schemas can be found here: http://www.edition-w3.de/TR/2001/REC-xmlschema-0-20010502/

1. XML as database

XSD schema for standard table structure:

<?xmlversion="1.0"?>
<xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleTypename="bigint">
<xsd:restrictionbase="xsd:long" />
</xsd:simpleType>
<xsd:simpleTypename="datetime">
<xsd:restrictionbase="xsd:dateTime" />
</xsd:simpleType>
<xsd:simpleTypename="ntext_80">
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="80" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleTypename="float">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>
<xsd:complexTypename="myTable_Double_Type">
<xsd:sequence>
<xsd:elementminOccurs="0"maxOccurs="unbounded"name="row">
<xsd:complexType>
<xsd:attributename="ID"type="bigint" />
<xsd:attributename="Timestamp"type="datetime" />
<xsd:attributename="Name"type="ntext_80" />
<xsd:attributename="Value" type="float" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:elementname="TestDB_XML">
<xsd:complexType>
<xsd:sequenceminOccurs="1"maxOccurs="1">
<xsd:elementname="myTable_Double"type="myTable_Double_Type" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

XML file for standard table structure (example):

<?xmlversion="1.0"encoding="UTF-8"?>
<TestDB_XMLxmlns:xs="http://www.w3.org/2001/XMLSchema-
nstance"xs:noNamespaceSchemaLocation="TestDB_XML.xsd">
<myTable_Double>
<rowID="1"Timestamp="2012-03-08T12:45:08"Name="TestValue1"Value="222.222" />
<rowID="2"Timestamp="2012-03-08T12:45:14"Name="TestValue1"Value="222.222" />
<rowID="3"Timestamp="2012-03-08T12:45:18"Name="TestValue1"Value="222.222" />
<rowID="4"Timestamp="2012-03-08T12:45:22"Name="TestValue1"Value="222.222" />
<rowID="5"Timestamp="2012-03-08T12:45:23"Name="TestValue1"Value="222.222" />
</myTable_Double>
</TestDB_XML>

Data types for XML tables:

<xsd:simpleTypename="bigint">
<xsd:restrictionbase="xsd:long" />
</xsd:simpleType>

<xsd:simpleTypename="datetime">
<xsd:restrictionbase="xsd:dateTime" />
</xsd:simpleType>

<xsd:simpleTypename="ntext_80"> //Länge kann individuell angegeben werden
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="80" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="float">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="binary_1"> //Länge kann individuell angegeben werden
<xsd:restrictionbase="xsd:hexBinary">
<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="bit">
<xsd:restrictionbase="xsd:boolean" />
</xsd:simpleType>

<xsd:simpleTypename="image_1"> //Länge kann individuell angegeben werden
<xsd:restrictionbase="xsd:hexBinary">
<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="integer">
<xsd:restrictionbase="xsd:int" />
</xsd:simpleType>

<xsd:simpleTypename="money">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="nchar_50"> //Länge kann individuell angegeben werden
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="50" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="nvarchar_50"> //Länge kann individuell angegeben 
werden
<xsd:restrictionbase="xsd:string">
<xsd:maxLengthvalue="50" />
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleTypename="real">
<xsd:restrictionbase="xsd:double" />
</xsd:simpleType>

<xsd:simpleTypename="smallint">
<xsd:restrictionbase="xsd:short" />
</xsd:simpleType>

<xsd:simpleTypename="tinyint">
<xsd:restrictionbase="xsd:byte" />
</xsd:simpleType>

<xsd:simpleTypename="varbinary_1"> //Länge kann individuell angegeben werden
<xsd:restrictionbase="xsd:hexBinary">
<xsd:maxLengthvalue="1" />
</xsd:restriction>
</xsd:simpleType>

Data type mapping between DB and PLC

E_ColumnTypes	XML	TwinCAT PLC
BigInt	bigint	T_ULARGE_INTEGER (TcUtilities.lib)
Integer	integer	DINT
SmallInt	smallint	INT
TinyInt	tinyint	BYTE
Bit_	bit	BOOL
Money	money	LREAL
Float	float	LREAL
Real_	real	LREAL
DateTime	datetime	DT
NText	ntext	STRING
NChar	nchar	STRING
Image	image	ARRAY OF BYTE
NVarChar	nvarchar	STRING
Binary	binary	ARRAY OF BYTE
VarBinary	varbinary	ARRAY OF BYTE

Creating/reading of records in/from the XML file

Standard SQL commands can be used for generating records. The TwinCAT Database Server interprets SQL INSERT commands and converts them for the XML file in the form of XML nodes. The TwinCAT Database Server converts SQL SELECT commands for the XML file in the form of XPath queries.

Samples for supported INSERT commands:

INSERT INTO myTable_Double (ID, Timestamp, Name, Value) VALUES(1, CURRENT_TIMESTAMP, 'TestValue1' , 1234.5678)
INSERT INTO myTable_Double (Timestamp, Name) VALUES(CURRENT_TIMESTAMP, 'TestValue1');
INSERT INTO myTable_Double VALUES(1, CURRENT_TIMESTAMP, 'TestValue1', 1234.5678);
INSERT INTO myTable_Double VALUES(1, '2010-01-06 12:13:14', 'TestValue1', 1234.5678);

Samples for supported SELECT commands:

SELECTID, Timestamp, Name, Value FROM myTable_Double;
SELECT* FROM myTable_Double;
SELECTTimestamp, Name FROM myTable_Double
SELECT* FROM myTable_Double WHERE Name = 'TestValue1';
SELECT* FROM myTable_Double WHERE ID > 1;

Supported function blocks:

FB_DBCreate
FB_DBCyclicRdWrt
FB_DBRead
FB_DBRecordArraySelect
FB_DBRecordDelete
FB_DBRecordInsert
FB_DBRecordInsert_EX
FB_DBRecordSelect
FB_DBRecordSelect_EX
FB_DBTableCreate
FB_DBWrite

2. XML standard XPath function

XPath types

The syntax of the prefixes of the XPaths in the TwinCAT Database Server is as follows: XPATH_[Type]<[Position]>#[Path]

There are 4 different types of XPath:

Reads data from the XML and returns them to the PLC

Appends the transferred data to the XML at the selected position.

Replaces the existing XML information at the selected position with the new data.

Deletes the data in the XML at the selected position.

There are 3 different data available for the positions:

ATTR

Applies to all attribute values from the selected XML tag.

Applies to the InnerText value of the selected XML tag.

SUBTAG

Applies to the InnerText value of all subtags of the selected XML tag.
If an XML Schema exists, the attributes are converted to the correct data types.
If no XML Schema exists, the attributes are returned as T_MaxString.

Samples:

XML file:

<?xmlversion="1.0"encoding="utf-8" ?>
<TestXML>
<Nodeattr1="1"attr2="Node1">
<SubNode1>SubNodeWert1</SubNode1>
<SubNode2>200</SubNode2>
<SubNode3>SubNodeWert3</SubNode3>
<SubNode4>400.5</SubNode4>
<SubNode5>SubNodeWert5</SubNode5>
</Node>
<Nodeattr1="2"attr2="Node2">
<SubNode1>SubNodeWert1</SubNode1>
<SubNode2>200</SubNode2>
<SubNode3>SubNodeWert3</SubNode3>
<SubNode4>400.5</SubNode4>
<SubNode5>SubNodeWert5</SubNode5>
</Node>
</TestXML>

XML Schema:

<?xmlversion="1.0"encoding="utf-8"?>
<xs:schemaattributeFormDefault="unqualified"elementFormDefault="qualified"xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:elementname="TestXML">
<xs:complexType>
<xs:sequence>
<xs:elementmaxOccurs="unbounded"name="Node">
<xs:complexType>
<xs:sequence>
<xs:elementname="SubNode1"type="xs:string" />
<xs:elementname="SubNode2"type="xs:short" />
<xs:elementname="SubNode3"type="xs:string" />
<xs:elementname="SubNode4"type="xs:double" />
<xs:elementname="SubNode5"type="xs:string" />
</xs:sequence>
<xs:attributename="attr1" type="xs:integer"use="required" />
<xs:attributename="attr2" type="xs:string"use="required" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Sample for XPATH<ATTR>

XPath => XPATH_SEL<ATTR>#TestXML/Node[@attr1=2]

Returned structure if no schema exists:

TYPEST_Record :
STRUCT
attr1 : T_MaxString := '2';
attr2 : T_MaxString := 'Node2';
END_STRUCT
END_TYPE

Returned structure if one schema exists:

TYPEST_Record :
STRUCT
attr1 : DINT := 2;
attr2 : T_MaxString := 'Node2';
END_STRUCT
END_TYPE

Sample for XPATH<TAG>

XPath => XPATH_SEL<TAG>#TestXML/Node[@attr1=2]/SubNode2

Returned value if no schema exists: SubNode2 : T_MaxString := '200';

Returned value if one schema exists: SubNode2 : INT := 200;

Sample for XPATH<SUBTAG>

XPath => XPATH_SEL<SUBTAG>#TestXML/Node[@attr1=2]

Returned structure if no schema exists:

TYPEST_Record :
STRUCT
SubNode1 : T_MaxString := 'SubNodeWert1';
SubNode2 : T_MaxString := '200';
SubNode3 : T_MaxString := 'SubNodeWert3';
SubNode4 : T_MaxString := '400.5';
SubNode5 : T_MaxString := 'SubNodeWert5';
END_STRUCT
END_TYPE

Returned structure if one schema exists:

TYPEST_Record :
STRUCT
SubNode1 : T_MaxString := 'SubNodeWert1';
SubNode2 : INT := 200;
SubNode3 : T_MaxString := 'SubNodeWert3';
SubNode4 : LREAL := 400.5;
SubNode5 : T_MaxString := 'SubNodeWert5';
END_STRUCT
END_TYPE

Special feature of the use of the FB_PLCDBCmd:

Unlike the usual implementation of the FB_PLCDBCmd, the set parameters (ST_ExpParameter) do not specify the placeholders for the respective instruction, but the scheme of the transferred or returned data.

Supported function blocks

FB_DBRecordSelect
FB_DBRecordSelect_EX
FB_DBRecordArraySelect