Retrieving binary data from base64 node in xml variable

In case there is xml document with base64 encoded binary data e.g. PDF (below example has the binary data shortened)

declare @doc xml
set @doc = '<doc><PdfData>JVBERi0xLjQN</PdfData></doc>'

then there is very simple to get the binary data decoded as binary again

select CAST (@doc.value('(//PdfData/text())[1]', 'varbinary(max)') AS varchar(max))

https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql

How to find size of databases and last time they got accessed

Sometimes you may need to free some space on MSSQL server and idetify databases which are not used long time back. This is especially useful on development servers where people may restore database just for troubleshooting of some issues and then the database is not needed anymore!

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)

select db.*, last_user_seek = MAX(last_user_seek),
 last_user_scan = MAX(last_user_scan),
 last_user_lookup = MAX(last_user_lookup),
 last_user_update = MAX(last_user_update)
from (
 select 
  name,
  (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
  (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB 
 from sys.databases db
) as db
left join sys.dm_db_index_usage_stats stats on stats.database_id = db_id(db.name)
group by db.name, db.DataFileSizeMB, db.LogFileSizeMB
order by db.DataFileSizeMB desc

References:
Sql server 2008 howto query all databases sizes
How do you find the last time a database was accessed

Removing duplicate records from database table

If there is need to select or remove data from table which are duplicate by multiple fields (field1 and field2) in the below example, there can be used query like

WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY field1, field2
                                       ORDER BY ( SELECT 0)) RN
         FROM   mst_table)
DELETE FROM cte
WHERE  RN > 1;

MSSQL server - How to return values from multiple nodes in XML in single field

DECLARE @xml xml
SET @Xml = '<FIELD NAME="NAME" BASE="5179827" COUNT="4">
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="b">...</TOKEN>
  <TOKEN TEXT="a">...</TOKEN>
  <TOKEN TEXT="c">...</TOKEN>
</FIELD>'

SELECT
  x.query('data(TOKEN/@TEXT)') AS List
 ,x.query('distinct-values(TOKEN/@TEXT)') AS DistinctList
 , x.query('<data>
    {
     for $x in distinct-values(TOKEN/@TEXT)
     return 
      (concat($x, ","))
    }
   </data>
 ').query('data/text()') AS CommaSeparatedList
FROM @xml.nodes('/FIELD') AS d(x)

References

http://www.olcot.co.uk/sql-blogs/using-xquery-to-remove-duplicate-values-or-duplicate-nodes-from-an-xml-instance

MSSQL server - how to read XML data - XPATH, sql:variable and local-name()

Example xml used for below queries

declare @xml xml
set @xml = '<Document>
 <Main>
  <Field_01>Field_01Value</Field_01>
  <Field_05>Field_05Value</Field_05>
  <TableFields>
   <Table_02>
    <Row>
     <Column_01>Table_02Row_01Col_01Value</Column_01>
     <Column_02>Table_02Row_01Col_01Value</Column_02>
    </Row>
    <Row>
     <Column_01>Table_02Row_02Col_01Value</Column_01>
     <Column_02>Table_02Row_02Col_02Value</Column_02>
    </Row>
   </Table_02>
  </TableFields>
 </Main>
</Document>'

Get the value of Field_01 tag

SELECT @xml.value('(//Field_01/text())[1]', 'nvarchar(max)')

Get the value of field in case you have the field name defined dynamically

declare @field varchar(10)
set @field = 'Field_05'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/text())[1]', 'nvarchar(max)')

Similar in case you need value of value of second row of column_01 in table defined dynamically

set @field = 'Table_02'
SELECT @xml.value('(//*[local-name()=sql:variable("@field")]/*/Column_01/text())[2]', 'nvarchar(max)')

MSSQL server - how to read XML data and join with another table data

This code snippet shows how to work with data stored in xml variable (or column of type xml). The xml data are joined with table and there are joined some data to output.

-- input
set @xml = '<docs>
<doc> <DocId>1000052</DocId><InvoiceNumber>S37</InvoiceNumber></doc>
<doc> <DocId>1000053</DocId><InvoiceNumber>S74</InvoiceNumber></doc>
<doc> <DocId>1000054</DocId><InvoiceNumber>E85</InvoiceNumber></doc>
</docs>'


SELECT
   p.value('(DocId)[1]', 'VARCHAR(10)') AS DocId,
   p.value('(InvoiceNumber)[1]', 'VARCHAR(100)') AS InvoiceNumber,
   ISNULL(d.SupplierID, '') AS Supplier
FROM @xml.nodes('/docs/doc') doc(p)
INNER JOIN Documents d ON d.DocId = p.value('(DocId)[1]', 'VARCHAR(10)')
FOR XML AUTO, ROOT ('docs')

Output:

<docs>
  <doc DocId="1000052" InvoiceNumber="S37" Supplier="Microsoft" />
  <doc DocId="1000053" InvoiceNumber="S74" Supplier="Amazon" />
  <doc DocId="1000054" InvoiceNumber="E85" Supplier="Sony" />
</docs>

Resources:

http://stackoverflow.com/questions/899313/select-values-from-xml-field-in-sql-server-2008

https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/

https://msdn.microsoft.com/en-us/library/ms178107.aspx

MSSQL server - how to generate XML 1

Xml can be generated in SQL server many different ways and it is not always straightforward

Requirement:

Generate elements with same names and some data as attributes and some as element inner text. 

Example:

<document>
  <fields>
    <field name="Field_1" type="Field1">Field1 Value</field>
    <field name="Field_2" type="Field2">Field2 Value</field>
  </fields>
</document>

Query:

select TOP 1
 'Field1' 'field/@type',
 'Field_1' 'field/@name',
 Field1Value AS 'field',
 '',
 'Field2' 'field/@type',
 'Field_2' 'field/@name',
 Field2Value AS 'field',
 ''
from Invoice as tbl1
for xml path('fields'), type, Root('Document')

Resources: 
http://stackoverflow.com/questions/25412429/sql-server-generating-xml-with-generic-field-elements