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