SXI Forum

LaurenD

XLayer Dev

Registered: 22-11-2018

Posts: 3

MSSQL's 'FOR XML' to easily structure data pulled from the database

When you want to pull multiple rows of data from one table and would like to process them all at once, MSSQL has a 'FOR XML' clause that you can use in the query that allows you to structure your data into XML elements.

Let's say you have a table with witness data, and you want all witnesses for one incident.
This is the data you would get from the database for accident id 34:

id	accidentid	name	        contact_number
18	34	        Paul Rosenburg	0792356671 
19	34	        Sandra Pages	0823198871

X-ServiceBroker will process all preceding mapping twice with the result returned above.
This is not what is required, we want to get both witnesses and not only one at a time.

To do this the following SQL query can be used:

SELECT(SELECT [id]
      ,[accidentid]
      ,[name]
      ,[contact_number]
  FROM witnessdetails where accidentid = 40 FOR XML PATH , root ('TopLevelElement') ) AS DATA

With this result being returned:

DATA 
<TopLevelElement>
    <row>
        <id>18</id>
        <headerid>40</headerid>
        <name>Paul Rosenburg</name>
        <contact_number>0798526541</contact_number>
    </row>
    <row>
        <id>19</id>
        <headerid>40</headerid>
        <name>Sean Pages</name>
        <contact_number>0114330469</contact_number>
    </row>
</TopLevelElement>

The 'PATH' mode allows us to specify that we want each column value in the resulting rowset to be wrapped in an element.
While the 'root (TopLevelElement)' option allows us to add a single top-level element called TopLevelElement to the data.

In X-ServiceBroker the DataDefinition to collect such a result would look as follows:

<sxi:DataDefinition name="CollectWitnessData">
      <sxi:Fields>
        <sxi:Field name="Data">
          <sxi:Rules>
            <sxi:XmlStringToMemory/>
          </sxi:Rules>
          <sxi:OutputField>witnessoutput</sxi:OutputField>
        </sxi:Field>
      </sxi:Fields>
    </sxi:DataDefinition>

There are plenty of option with this FOR XML clause.

Here is the link to the official docs:
https://docs.microsoft.com/en-us/sql/re … erver-2017