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:
- SEL
- Reads data from the XML and returns them to the PLC
- ADD
- Appends the transferred data to the XML at the selected position.
- UPD
- Replaces the existing XML information at the selected position with the new data.
- DEL
- 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.
- 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