Inserting XML into SQL DB and then querying it



i have a program that inserts XML data into an XML column in MSSQL, and i am finding that SQL is changing the formatting of the data of the source XML on insert. This is making it very difficult to query the data afterwards using the same same source XML.


For example, an extremely simple demo to show the issue:



INSERT INTO XMLRecord (XMLData) VALUES ('<Test></Test>')

Select * from XMLrecord

Returns: <Test />


Now, if i try to run a query to see if my initial XML exists in the database:



select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test></Test>'

0 rows returned;


However, if the source XML doesn't contain "empty" tags, then the insert and searching works fine:



INSERT INTO XMLRecord (XMLData) VALUES ('<Test>Test Values</Test>')

Select * from XMLrecord

Returns: <Test>Test Values</Test>


Now, if i try to run a query to see if my initial XML exists in the database:



select count (*) as 'Count' from XMLRecord where cast (XMLData AS NVARCHAR(MAX)) = '<Test>Test Values</Test>'

1 rows returned;


I am hoping somebody can assist with a solution on how to get this to work. How can i take a source XML block (of unknown size, content etc), insert it into the database and then query the database for an EXACT match on the XML that i just inserted.


The data needs to be stored in the DB as XML format as i am also using it for numerous other xQueries.


Thanks very much.


No comments:

Post a Comment