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

ASP.NET webforms URL routing

URL routing is very cool functionality available for ASP.NET applications. It allows us to make the application URLs more user friendly. That was not the root key why I decided to use it in our application.

Application renders the PDF files on output dynamically from images in database and show it to users. They can see it in browser in Adobe Reader. There is one functionality in Adobe Reader which allows to send PDF file by email, using email client on the computer. From some version the attachment started to have aspx extension which forces user to rename it before it can be actually opened.

Simple solution to this is to use URL routing functionality to render the file to output as if it is really PDF.

Below are few links which I found useful during implementation and deployment of the application.


Routing with ASP.NET Web Forms
URL routing is not working in IIS 6
Troubleshooting ASP.NET routing on IIS 7

Windows phone 8 emulator

I have come accross these problems when installing Windows phone 8 emulator in vmware player.

1. CPU
- needs to support virtualization and it needs to be enabled in BIOS

2. operating system
- 64 bit
- needs to be some which supports SLAT (Windows 8, Windows Server 2012 ...)
- needs to have Hyper-V enabled

3. VMware settings
- Edit vmx file and add this configuration hypervisor.cpuid.v0 = "FALSE",  mce.enable = "TRUE"
- Processors setting - enable Virtualize Intel VT-x/EPT or AMD-V/RVI
- Processors setting - setup at least two cores
- Memory settings - setup enough memory for virtual machine

4. Windows Mobile 8 SDK
- will download and install all required prerequisities, it is good to have prepared steps above


Sources:
Windows 8 Hyper-V will require SLAT (Second Level Address Translation)
MachineSLATStatusCheck
Windows Server 2012 and Hyper-V
Hyper-V in Workstation 8
Windows Phone SDK

MSSQL trace file analysis

Today I was looking for a way how to access MSSQL server trace data from TSQL. The solution is to import data to database via function fn_trace_gettable.

SELECT * INTO temp_trc
FROM fn_trace_gettable('c:\temp\trace.trc', default);

Issue to restore of replicated database solved

Restore of backuped database which used replications can be a problem. Database was restored successfully but it was still marked as part of publication (see is_published in sys.databases). There was not possible to truncate, alter structure of tables etc.

This issue can be resolved by running this command on database:

exec sp_removedbreplication 'database_name'

How to Export Image column to file on MSSQL II

Another way how to export binary data from database. It is possible to use bcp utility.

bcp "SELECT img FROM db_name.dbo.Images WHERE ID = 1" queryout "c:\001.jpg" -T -S server

Once you run it you will be asked for the file storage type (no change required), prefix-length (needs to be 0), length of field and field terminator (no change)