Tag: XML

Querying XML in SQL

I like to use my blog as a library for myself, to be able to easily find solutions I have used before.  Here is a simple example of querying an XML string using SQL. 

In this example I have inserted one XML string into a temporary table, with two items called pages, and two reference of type ‘book’.  The resulting select statements will pull any and all pages from the XML string, and any ‘book’ references.  There can be multiple types of references with varying fields, which you can query in the same way, simply be altering the text in the .nodes in the FROM statement. 

    DECLARE @xml as XML
    DECLARE @tblXML AS TABLE  (Response xml )
INSERT INTO @tblXML
SELECT '<response>
  <pages>
    <page>
      <id>1376745</id>
      <name>First Page</name>
     </page>
    <page>
      <id>1376746</id>
      <name>Second Page</name>
     </page>  </pages>
  <references>
    <reference>
      <type>book</type>
      <id>425285</id>
      <name>How I met your mother</name>
      <activestatus>false</activestatus>
    </reference>
    <reference>
      <type>book</type>
      <id>425286</id>
      <name>Covered in Bees</name>
      <activestatus>true</activestatus>
    </reference>
  </references>
</response>' as Response


SELECT @xml = Response from @tblXML

            SELECT 
                ID        =    t.item.value('(id)[1]','int'),
                BookName    =    t.item.value('(name)[1]', 'nvarchar(50)'),
                IsActive    =    case when t.item.value('(activestatus)[1]', 'nvarchar(10)') = 'false' THEN cast(0 as bit) ELSE cast(1 as bit) END
            FROM @xml.nodes('/response/references/reference[type/text() = "book"]') as t(item)


            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @xml.nodes('/response/pages/page') as t(item)

You can also query a table directly rather than storing the XML in a variable by using CROSS APPLY, like this.

            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @tblXML x
            CROSS APPLY
            Response.nodes ('/response/pages/page') as t(item)

I hope you find this useful.

Exporting InfoPath repeating table data with Nintex Workflow

A client wanted to store the data from an InfoPath form with a repeating table as a step in a Nintex Workflow.  Users were submitting expenses in SharePoint and could have multiple expenses in the form. The data was pulled out of the InfoPath form by querying the XML, and was stored in the workflow variables for use further in the workflow. The relevant portions of the workflow look like this:

imageimage

image
GET LINE ITEMS

Add a Query XML action to your Nintex workflow called “Get line items”.  Set up a variable call LineItems which is of Collection type.  Configure the action as follows:

image

If you don’t know the XPath you can look at your form in InfoPath, right click on the field you are interested in, and select Copy XPath.  This will copy it to your clipboard, and you can paste it into your action.

image

FOR EACH LINE ITEM

Add a For Each action to your workflow called “For each line item”. This will loop through each of the items queried in Get Line Items, above, and store the current line in a text variable. Add a variable to your workflow called LineItem which is a Single line of text.  Configure the action as follows:

image

EXTRACT DATA FROM LINE ITEMS

Add the relevant logic to your workflow and incorporate the Query XML actions to pull the individual fields you want from your InfoPath form.  From the screenshot of the flow, above, you would configure Extract Submit Date as follows:

image

The relevant data points you have queried in your Query XML actions are stored in the workflow variables, and are ready to be used to build and execute a SQL Insert statement during each loop.  (***UDATE 2014-08-11:  A reader has pointed out that the XML source in this screenshot should actually be the string variable “Line Item”.  I will check this at a later date, but am updating the post to point readers in the right direction.***)

BUILD SQL INSERT STATEMENT

Drag a Build String action into your workflow, within the For Each loop.  Add a variable called InsertStatementForSQL of type Single line of text .  Build your insert statement in the Text portion of the action and store it in the InsertStatementForSQL variable.  Use the Insert Reference button to insert the workflow variables.  Note that if you are inserting a string value from a variable into the SQL table you need to surround it with single quotes so SQL will recognize it as a string.

image

EXECUTE SQL STATEMENT

Drag an Execute SQL action onto your workflow, within the For Each loop.  In this case I’ve used a Workflow Constant for the connection.  You can configure your connection string as necessary.  In the query, click on the Insert Reference button and select the InsertStatementForSQL variable you populated in the previous step.

image

Voila, your workflow will loop through each repeating line of your form and build and execute a SQL statement inserting that data into a table.

In this case the client also want the data displayed in an email.  My next post talks about that – Creating an HTML Table in a Nintex Workflow