title: Converting Varbinary into XML SQL Server With date pattern mm/dd/yyyy HH:mm
body: <p>I am trying to query my database for a delimited file that is saved as a <code>varbinary</code>. I am running the following query.</p>
  
  <pre><code>SELECT TOP 2 CAST('&lt;![CDATA[' + CONVERT(NVARCHAR(MAX), Content, 101) + ']]&gt;' AS XML)
  FROM FileTable WITH (NOLOCK)
  WHERE CreatedBy = &lt;userID&gt;
  ORDER BY CreatedOn DESC
  </code></pre>
  
  <p>Content of file looks something like this</p>
  
  <pre><code>04/10/2020|55555555|098765432109|Lorem Ipsum|44 lorem|04/10/2020 17:46|04/10/2020 18:31|22|Lorem Ipsum Text|Lorem, Ipsum|(111) 111-9999|TXT|Lorem Ipsum Text Test|Lorem Ipsum|Lorem|A1PH4NUM3|LoremIpsum|Lorem Ipsum Test, Lorem Ipsum Text|A22.2|
  </code></pre>
  
  <p>Some of the files convert fine while others turn into a blob of Chinese characters. The files that turn into Chinese characters have a date format that looks like this mm/dd/yy HH:mm. My hunch is that this format is what is causing the problem since I am using date_style as 101. </p>
  
  <p>Can you advise me on how can I get around this problem?</p>