Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Wednesday, January 3, 2018

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

Thursday, November 16, 2017

SQL - Use PARSENAME function to extract individual parts from a string

Today I discovered a very cool feature in T-SQL from Microsoft SQL Server.
It happens (unfortunately) again and again that several records are written in a single column and one is forced to extract individual parts from it. So far, I have always written a SQL function of my own, but this is will be history from now on with the help of the PARSENAME function. :-)

See this examples. I think, this explains it very well:

DECLARE @exampleString nvarchar(max) = 'Test;Test1;Test2' 
SELECT PARSENAME(REPLACE(@exampleString,';','.'),1)

--Result: Test2

SELECT PARSENAME(REPLACE(@exampleString,';','.'),2)

--Result: Test1

SELECT PARSENAME(REPLACE(@exampleString,';','.'),3)

--Result: Test