Convert DateTime to ISO8601 for use in SharePoint CAML queries Ever wanted to create a CML query and insert a DateTime value? No such luck, DateTime values cannot be inserted directly, you need to convert the DateTime value to ISO8601 format and there is no DateTime function to do that.

• Sharepoint List Caml Query
• Sql To Caml Query Converter Temperatures

1. I was hoping to do convert a CAML query into T-SQL. For my SharePoint site, I also have a data warehouse (SQL Server 2005) which hosts the same data (it is populated through SPList EventReceivers: Stack Overflow. SharePoint CAML Query to T-SQL. Ask Question 2.
2. I have a simple query SQL query I am trying to translate into CAML but. Stack Exchange Network. Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn. Translating a SQL query to CAML query.

I am importing sql into my Access database and am working on parsing the data into the correct tables and fields. I have run into an issue as my import creates column names and enters the values into the columns but the database uses those column names as row values and the values in a separate column

Current table

table I am attempting to parse into

I have done a union query to get the results column to populate nicely but I cannot get the column names to parseIs there a way to do this?

3 Answers

Since you are using MS Access, there is no UNPIVOT function so you can use a UNION ALL query:

As a side note, when you are doing a UNION ALL or UNPIVOT, the datatypes must be the same so you might have to convert the data in the val column so it is the same.

You can create a VBA function similar to this one:

Sharepoint List Caml Query

With this you can extract field names and add the code needed to compose a SQL string for insert data in your normalized table.

If you wish to avoid SQL code directly you can do the above using the Access Query editor.

Select all the fields in your table after pressing the Query Design button from the design toolbar.

Remove the fields SC, DO, temp, pH etc.

In the query editor tab, add new fields:

• Characteristic_Name: 'SC'
• Characteristic_Value: SC

and press the make table button on the design toolbar, choose a new table name and then run the query to create this table.

Then open the same query again and do the following field edits:

• Characteristic_Name: 'DO'
• Characteristic_Value: DO

and press the append table button on the design toolbar.

Choose the same new table name as above and run the query.

Check that you have double the number of rows.

Repeat again for edits:

• Characteristic_Name: 'temp'
• Characteristic_Value: temp

etc.

At the end check you have multiplied the number of rows by the number of columns you wish to drop.

Although not as quick as the above anyone can use on their own problem, especially those who are worried about getting code wrong.

Sql To Caml Query Converter Temperatures

Not the answer you're looking for? Browse other questions tagged sqlxmldatabasems-access-2010 or ask your own question.