Getting contents of XML File into a variable using sqlcmd ":r"

Question

I have an XML file which I want to use to load static data into my database. Preferably, I would like to use the sqlcmd ":r" to do a text replace so that the xml can be treated as hard coded data in my sql script.

With hard coded data, my script works

--Working Code
DECLARE @XML XML
SET @XML = '
<clients>
    <client>
        <ClientNumber>ClientA</ClientNumber>
    </client>
    <client>
        <ClientNumber>ClientB</ClientNumber>
    </client>
</clients>
'

What I would like to do is something like this instead:

DECLARE @XML XML
SET @XML = '
:r .\ClientData.xml
'

Where the contents of ClientData.xml is the same as the previously hard coded xml text. The problem is that the sqlcmd parser is not picking up on the test replace, and is instead just putting the value ':r .\ClientData.xml' in my xml variable.

If I try removing the tics, I get the following error:

Incorrect syntax near '<'.

Is there any way I can force sqlcmd to execute when inside tics so it text replaces properly?

Answer 1

Ok, Here's my hack...

:setvar XMLDATA "'"

declare @mydata xml = 
$(XMLDATA)
:r "Data.xml"
$(XMLDATA)

Basically just works around the parser by parameterizing the single quote.

It's ugly, but it allows me to have a valid xml file.