Insert XML file into SQL Server 2005 using XML datatype

[ChrisReed]

I have defined a column in MS SQL server 2005 to be of the "new" XML datatype format.  I then use the NI Database Toolkit - INSERT DATA.vi  (LabVIEW 2009) to insert an XML file contents into this column.  However when I read the file out from the database it seems that a lot of characters are missing eg. the header, all returns and new line characters.

However if I define the column as VARCHAR(max) I can insert the XML file contents into the database and read it out with no problems.

So I am not sure if the database is altering the file contents when I do an insert for the XML datatype, because I have not setup the column properly OR the NI Insert Data VI is not inserting the XML file contents properly.

Any ideas - thanks Chris.

[Tanya_V]

Hi Chris,

I have not run into this particular issue before but here are a couple of things to try to see if they change the behavior.

Have you tried inserting the data another way, not through LabVIEW? This could help determine if the Insert Data VI is the problem.

Have you also tried setting the 'flatten cluster?' input to true?

Let me know what comes of these tests!

[ChrisReed]

Hi Tanya,

I tried inserting data through Access 2003, but it returned an error....

"....XML parsing: Line 1, character 42, unable to switch the encoding (#9402)"  -> which happens to be the \r character.

Looking this error up on the web it talks about changing from UTF-8 to UTF-16 encoding so I changed the topline in my XML file from

<?xml version="1.0" encoding="iso8859-1"?>   to    <?xml version="1.0" encoding="UTF-16"?>

and Access then inserted the data row, BUT.... it did the same thing as LabVIEW ie. it removed this top line and all the \r\n characters.

I also tried setting the 'flatten cluster' input to true, but the data must then be inserted into a binary column instead of individual columns with their own datatype which is what we need - so it returned an error due to column mismatch.

I think? that perhaps you can't use the standard INSERT command when inserting XML data into an XML datatype column - instead maybe one has to use something like XML_INSERT to tell the database that the following data is XML - so just insert it into the column without altering it?

Chris

[Tanya_V]

Hi Chris,

It looks like it's the database that is modifying the data and removing characters, since this happens even when LabVIEW is cut out of the loop. I would, therefore, recommend contacting Microsoft to discuss why this is happening. You will get to the root of the problem faster.

Take care!

[ChrisReed]

Well I've managed to successfully write XML data into a column defined as XML - but to do this I had to:

1) Use the OLE DB Provider Connection string instead of ODBC Driver to INSERT the XML data.

2) and perhaps upgrade from SQL Server 2005 to 2008 (not sure if this is required).

Now can anyone help me on what the SQL syntax is to perform a QUERY on this XML data?  Looking on the web there just seems to be sooooo.... many different ways of doing this.  When I try them with LabVIEW some return syntax errors, and some just return blank data.

eg. I have a table called "results" which has an XML column called "mtr_file" and the XML file has an Attribute called <SerialNumber>.  If I want to know what the value of this attribute is I tried the following SQL query....

SELECT mtr_file.query('data(/SerialNumber)') FROM results  -> No error, but empty results

If I try...

SELECT mtr_file.query('data(/SerialNumber)') FROM results   -> error value function requires 2 arguments

I just want to be able to query the value of an Attribute in the XML file data - how hard can that be

Chris