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 = ' ClientA ClientB ' ``` 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?

Accepted Answer

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.

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

Solution

Problem