Query XML with SQL

I've just learned how to query an xml structure with SQL.
I'm presenting you a simple example. Assuming your XML column looks like this:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.xyz.de/activerepository/fileprops">
  <props>
    <prop ns="ARM:" elem="_NoFilter">
      <value xsi:type="xsd:boolean">true</value>
    </prop>
    <prop ns="DAV:" elem="displayname">
      <value xsi:type="xsd:string">Some text in it</value>
    </prop>
    <prop ns="DAV:" elem="getcontenttype">
      <value xsi:type="xsd:string">message/rfc822</value>
    </prop>
    <prop ns="DAV:" elem="creationdate">
      <value xsi:type="xsd:dateTime">2017-01-02T09:38:28.1278078</value>
    </prop>
  </props>
</root>

...you can write this query to get single values from the XML. In my example, I want to query the displayname prop.

SELECT 
  CAST(properties as xml).value('(/root/props/prop[@elem="displayname"]/value)[1]','nvarchar(max)') as [displayname],
  *
FROM   

  tm_cas_files (nolock)

The result will be:

displayname

Some text in it

See also this Stackoverflow link to get more details:
https://stackoverflow.com/questions/48075328/sql-xml-how-to-query-specific-node

SQL Query - Get multiple row results in one column with XML and STUFF()

Today I want to show you, how you can put the results of an SQL query into one column.

First I create a temporarily table for our data and fill some data in it:

CREATE TABLE #table (product nvarchar(255)) 

INSERT INTO #table
SELECT 'product-1' as product
UNION 
SELECT 'product-2' as product
UNION 
SELECT 'product-3' as product
UNION 
SELECT 'product-4' as product


Now we have our base table for our query. Here are the results of this table

Query:
select * from #table

Results:
product
product-1
product-2
product-3
product-4

Now we can see, we have 4 rows with our example products. Maybe we have a requirement to put all products in one row. This can be done by using the STUFF function and XML.
Here is the code:

select STUFF((SELECT distinct ',' + t.product
                    from #table t (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as allProducts

Now the result of this query is this:

allProducts
product-1,product-2,product-3,product-4


Hope you enjoyed this lesson.

SharePoint 2010 - Building Custom Actions in Visual Studio

Today I will show how you can add custom actions to the ribbon bar and the edit control block in SharePoint.

We have to start with creating an empty element in the project. Then replace the text in the xml file with this:

<?xml version="1.0" encoding="utf-8"?>
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <CustomAction
  Description="Approve Documents"
  Title="Approve Documents"
  Id="RibbonDocumentsManageApproveDocuments"
  Location="CommandUI.Ribbon"
  RegistrationId="10000"
  RegistrationType="List"
  Sequence="0"
  xmlns="http://schemas.microsoft.com/sharepoint/">
    <CommandUIExtension xmlns="http://schemas.microsoft.com/sharepoint/">
      <!-- Define the (UI) button to be used for this custom action -->
      <CommandUIDefinitions>
        <CommandUIDefinition Location="Ribbon.Documents.Manage.Controls._children">
          <Button Id="Ribbon.Documents.Manage.ApproveDocuments"
          Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}"
          Image32by32="~site/_layouts/Images/SharePoint-Z-Drive-Project/approve_document_32x32.png"
          Image16by16="~site/_layouts/Images/SharePoint-Z-Drive-Project/approve_document_16x16.png"
          Sequence="0"
          LabelText="Approve Documents"
          Description="Approve Documents"
          TemplateAlias="o1" />
        </CommandUIDefinition>
      </CommandUIDefinitions>
      <CommandUIHandlers>
        <!-- Define the action expected on the button click -->
        <CommandUIHandler Command="{4E2F5DC0-FE2C-4466-BB2D-3ED0D1917763}" CommandAction="javascript:window.open('http://www.bing.com/search?q='.concat(escape(document.title)))" />
      </CommandUIHandlers>
    </CommandUIExtension>
  </CustomAction>

  <CustomAction
  Description="Approve Document"
  Title="Approve Document"
  Id="EditControlBlockApproveDocument"
  Location="EditControlBlock"
  RegistrationId="10000"
  RegistrationType="List"
  ImageUrl="~site/_layouts/Images/SharePoint-Z-Drive-Project/approve_document_16x16.png"
  Sequence="1101"
  xmlns="http://schemas.microsoft.com/sharepoint/">
    <CommandUIExtension xmlns="http://schemas.microsoft.com/sharepoint/">
      <!-- Define the (UI) button to be used for this custom action -->
      <CommandUIDefinitions>
        <CommandUIDefinition Location="EditControlBlock">
          <Button Id="EditControlBlock.ApproveDocument"
          Command="{F8C45D3A-A00D-4412-91BB-C49D75A36F3A}"
          Sequence="1101"
          LabelText="Approve Document"
          Description="Approve Document"
          TemplateAlias="o2" />
        </CommandUIDefinition>
      </CommandUIDefinitions>
      <CommandUIHandlers>
        <!-- Define the action expected on the button click -->
        <CommandUIHandler Command="{F8C45D3A-A00D-4412-91BB-C49D75A36F3A}" CommandAction="javascript:window.open('http://www.bing.com/')" />
      </CommandUIHandlers>
    </CommandUIExtension>
  </CustomAction>
</Elements>

The first custom action is to add the button to ribbon bar in the document library. 

The second custom action will add the button to the edit control block of an item.

SharePoint 2010 - Adding your created visual web part to the schema.xml of a list definition

Hi guys,

Today I tried to add a visual webpart to a list definition, especially to an indidividual edit form of the list definition.
I created a visual webpart in visual studio and a list definition with a list instance.
For the list defintion, I wanted to add a custom editform.aspx in the <forms> area of the schema.xml.
That can be done this way:

Find the <forms> area in the schema.xml:

    <Forms>
      <Form Type="DisplayForm" SetupPath="pages\form.aspx" Url="Forms/DispForm.aspx" WebPartZoneID="Main" />
      <Form Type="EditForm" SetupPath="pages\form.aspx" Url="Forms/EditForm.aspx" WebPartZoneID="Main" />
      <Form Type="NewForm" Url="Forms/Upload.aspx" WebPartZoneID="Main" />
...

We see the 3 standard forms (display, edit and new). Now we can add a custom editform.aspx by defining a new form with the type "editform":

      <Form Type="EditForm"
            SetupPath="pages\form.aspx"
            WebPartZoneID="Main"
            Url="Forms/MyEditForm.aspx"
            Default="TRUE"></Form>

Important are the "type", "url" and the "default" property. The type as the name says, defines the type of the form. The url property can be defined free. You can enter there any name. And the last property "default", if this form will be the standard / default for the specific type.

If we start our solution now, we would have the same result as the standard editform.aspx. There would be no difference for the user. We, of course, can see that the url is different than the standard editform.aspx.

Now we want to add another webpart to our new editform.aspx (in my case myeditform.aspx):
First we have to add a container for all webparts, we want to add to the myeditform.aspx. This code has to placed between the <form> tags:

<Form Type="EditForm"
            SetupPath="pages\form.aspx"
            WebPartZoneID="Main"
            Url="Forms/MyEditForm.aspx"
            Default="TRUE">
        <WebParts>

        </WebParts>
</Form>

Then we need to add the webpart itself:

<Form Type="EditForm"
            SetupPath="pages\form.aspx"
            WebPartZoneID="Main"
            Url="Forms/MyEditForm.aspx"
            Default="TRUE">
        <WebParts>
          <AllUsersWebPart WebPartZoneID="Main" WebPartOrder="1">
            <![CDATA[

            ]]>
          </AllUsersWebPart>
        </WebParts>
</Form>

In the cdata area we can copy the complete code of our visual webpart file (*.webpart) except the first line:

<?xml version="1.0" encoding="utf-8"?>

So we get this result in the end:

<Form Type="EditForm"
            SetupPath="pages\form.aspx"
            WebPartZoneID="Main"
            Url="Forms/MyEditForm.aspx"
            Default="TRUE">
        <WebParts>
          <AllUsersWebPart WebPartZoneID="Main" WebPartOrder="1">
            <![CDATA[
<webParts>
  <webPart xmlns="http://schemas.microsoft.com/WebPart/v3">
    <metaData>
      <type name="SharePoint_Z_Drive_Project.WP_ErrorField.WP_ErrorField, $SharePoint.Project.AssemblyFullName$" />
      <importErrorMessage>$Resources:core,ImportErrorMessage;</importErrorMessage>
    </metaData>
    <data>
      <properties>
        <property name="Title" type="string">Error</property>
        <property name="Description" type="string">Error Field WebPart</property>
      </properties>
    </data>
  </webPart>
</webParts>
            ]]>
          </AllUsersWebPart>
        </WebParts>
</Form>

If we start the solution now and edit an item in our list instance, we see the properties of our item and our visual webpart.

SQL Server 2008 - Query XML data

Just recognized, that it is easy to query xml data stored in a SQL Server 2008 table (field):

declare @data xml

select @data = <datafield> from <table> (nolock) where <condition>

select @dataselect @data.query('<node>') as result

Here an example with sample xml:

declare @data xml

select @data = '<root><datas><data><add name="a" value="x1" /><add name="b" value="y2" /> <add name="c" value="z3" /></data><data><add name="a" value="e4" /> <add name="b" value="f5" /><add name="c" value="g6" /> </data></datas></root>' 

select @data

select @data.query('(/root/datas/data/add[@name="c"])[1]') as result 

The result is:

<add name="c" value="z3" /> 

Now I need to find out, how to get the value and not the complete node.