/
GetSqlQueryResultAsXml

GetSqlQueryResultAsXml

Oct 01, 2015

What to use it for

Prerequisites for usage

Essential properties to set

  • Only relevant if you want to interact with a database.
  • Use a GetSqlQueryResultsAsXml to get data from a database, which you want to receive in XML format.
  • It will produce an XML file.
  • Available for SmartBridge 3 and up.
  • Access to a database.
  • <T>
  • Connection String
  • Command Type
  • SQL Query
  • Parameters

<T>

Activities with a <T> will ask you to indicate what the data type is of the data that will be affected. Depending on the data type, you will get a different Template (<T>) with configuration options.

 

 Learn more about <T>

Activities with a <T> will ask you to indicate what the data type is of the data that will be affected. A database contains data of several types, and the <T> indicates that configuring that Activity starts with setting the targeted data type.

Depending on the data type, some configuration options will (dis)appear, depending on their relevancy.

When this Activity will affect this type of database data..Then select this option
BooleanBoolean
Whole numbersInt32
Text and/or numbersString
Object (with properties)Object
SmartBridge documentTie.MessagePortal.Shared.Entities.Document
Content of a SmartBridge documentTie.MessagePortal.Shared.Entities.DocumentContent
State of a SmartBridge documentTie.MessagePortal.Shared.Entities.DocumentState
A list of data of a specific data typeArray of (T)
Data specific to SmartBridgeBrowse for types...

Misc

OptionWhat is itHow to use it

Connection string


 

A connection string is a line of code for connecting with and accessing a database.

 Read more

About connection strings

A connection string is a line of code for connecting with and accessing a database. An example:

"Data Source=localhost;Initial Catalog=TieEArchive;Integrated Security=SSPI"

Make sure you use double quotes around the connection string.

Learn about connection strings

 

Write a connection string (see explanation), between double quotes.

Command type


 

The type of SQL query.

Indicate what type of query you will be using:

  • Text - To execute a standard query (default)
  • Stored procedure - To execute a reusable SQL code that is stored in the database.

SQL query


 

The actual SQL query to request the relevant data from the database.

Enter the SQL query, between double quotes.

Use a parameterized query when using a text query, to prevent being vulnerable to SQL injection attacks.

 Read more

Example of a parameterized query:

select * from user where age = @age

Instead of entering the actual age, you should use the name of the variables ('parameter') that represents the actual value.

Define these parameters using the 'Parameters' option.

Parameters


 

Instead of hardcoding values in your SQL queries a better way is to use parameters: A parameter is a variable in an SQL query that represents a predefined value. Use the current 'Parameters' option to create these predefined values.

Provide a name for your new variable, and a corresponding value.

 How to use query parameters

About query parameters

To prevent SQL injection attacks it is recommended to make use of parameterized queries instead of hardcoding values.

Using query parameters

Example case 1

Say you want to get a column containing a specific value, such as 'age = 45'.

Create the following parameter
NameDirectionTypeValue
ageinint3245
Use the parameter in your query
select * from user where age = @age
Example case 2

Say you want to retrieve the age of user 'Jane'.

Create the following parameter
NameDirectionTypeValue
nameinstringJane
Use the parameter in your query
select age from user where name = @name

Direction

DirectionRelevant forUse when your value will be
In
  • Parametrized query
  • Stored procedure
Fixed value, or variable
Out
  • Stored procedure
Argument
In/Out
  • Stored procedure
Special return value

Display nameName of the Activity.Leave at the default, or enter a name that is a better reflection of the type of query that is executed.

OptionWhat is itHow to use it
Local pathFile path to XML you want to write the result to.Enter an absolute path that includes the file name.

Resulting XML

<?xml version="1.0" encoding="utf-8" ?>
<RecordSet>
  <Record>
    <Column name="Id" clrType="System.Int64" dbType="bigint">1</Column>
    <Column name="Name" clrType="System.String" dbType="nvarchar">Jack</Column>
    <Column name="Age" clrType="System.Int32" dbType="int">51</Column>
  </Record>
  <Record>
    <Column name="Id" clrType="System.Int64" dbType="bigint">2</Column>
    <Column name="Name" clrType="System.String" dbType="nvarchar">Jill</Column>
    <Column name="Age" clrType="System.Int32" dbType="int">48</Column>
  </Record>
</RecordSet>

All SQL query Activities