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