Snippet: Schema
We're having a hard time with SQL 05's xml.
In a nutshell, I have a schema. I have used .Net 2.0's xsd tool to create classes from this schema.
This schema is also tied to a XML column in my database server. So far so good.
However, when I go to deserialize the xml data stored in the database server I'm getting inconsistent results. Namely the differences in the following queries:
SELECT LoanXML.query('declare namespace MISMO="http://mrgdev.local/mismo/";//MISMO:LOAN')
FROM Loans
WHERE Loans.InternalID = @LoanID
returns something completely different from:
SELECT LoanXML from Loans where InternalID = @LoanID
The difference is that the first query throws xsi:nil=true into EVERY SINGLE NODE. Whereas the second one doesn't.
When we go to deserialize, then the nodes aren't converting into objects even though they are properly filled out...
I'd just chuck the .query syntax, except for a little problem where I'm trying to return just the node(s) I want instead of the whole thing.
Is there anyone else using xml deserialization with SQL 05?
The following creates a table and inserts a row with xml data into it.
CREATE TABLE [dbo].[MyTest](
[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_mytest_id] DEFAULT (newid()),
[test] [xml](CONTENT [dbo].[ChrisSchema]) NULL,
CONSTRAINT [PK_chris] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into MyTest(test)
values('<LOAN xmlns="http://mrgdev.local/test/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://mrgdev.local/test/">
<_APPLICATION>
<_DATA_INFORMATION>
<DATA_VERSION _Name="DVTESt" _Number="123123"/>
</_DATA_INFORMATION>
</_APPLICATION>
</LOAN>')
Now, execute
SELECT test.query('declare namespace test="http://mrgdev.local/test/";//test:LOAN')
FROM MyTestYou're result will be:
<LOAN xmlns="http://mrgdev.local/test/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://mrgdev.local/test/">
<_APPLICATION>
<_DATA_INFORMATION>
<DATA_VERSION _Name="DVTESt" _Number="123123" xsi:nil="true" />
</_DATA_INFORMATION>
</_APPLICATION>
</LOAN>Notice the xsi:nil=true... First off, that's wrong. It isn't nil. Second, this will not deserialize. WTF?
If I just run
select test from MyTest
then the data looks like:
<LOAN xmlns="http://mrgdev.local/test/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://mrgdev.local/test/">
<_APPLICATION>
<_DATA_INFORMATION>
<DATA_VERSION _Name="DVTESt" _Number="123123" />
</_DATA_INFORMATION>
</_APPLICATION>
</LOAN>
In this case the data_version node correctly comes back WITHOUT the xsi:nil="true"
In addition, if we UNTYPE the xml column, then it will also come back without the xsi:nil="true"garbage; but that's not a real option.
How do we get the typed version to not do this? I even tried placing the definition for DATA_VERSION in a different schema, but this had zero effect on our output.
After all of our testing, it appears that if an element only has attributes then it reports the element as xsi:nil="true" which in our case is wrong, because our elements only have attributes.
Is there some way to get around this?
