Deserialize xml data from sql 2005

Question

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?

Answer 1

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://mrgdev.local/mismo/" targetNamespace="http://mrgdev.local/mismo/" elementFormDefault="qualified">
    <xs:import namespace="http://www.w3.org/XML/1998/namespace" schemaLocation=".\xml.xsd"/>
    <xs:element name="LOAN" nillable="false">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="_APPLICATION" nillable="true" minOccurs="0">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="_DATA_INFORMATION" nillable="true" minOccurs="0">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element ref="DATA_VERSION"/>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                      </xs:sequence>
                </xs:complexType>
          </xs:element>
       </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="DATA_VERSION" nillable="true">
        <xs:complexType>
            <xs:attribute name="_Name" type="xs:string" use="required"/>
            <xs:attribute name="_Number" type="xs:string" use="required"/>
        </xs:complexType>
    </xs:element>
</xs:schema>

Answer 2

CREATE XML SCHEMA COLLECTION [dbo].[ChrisSchema]
AS N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="http://mrgdev.local/test/"
xmlns:dv="http://mrgdev.local/DV/"
targetNamespace="http://mrgdev.local/test/" elementFormDefault="qualified">
    <xs:import namespace="http://www.w3.org/XML/1998/namespace" schemaLocation=".\xml.xsd"/>
    <xs:element name="LOAN" nillable="false">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="_APPLICATION" nillable="true" minOccurs="0">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="_DATA_INFORMATION" nillable="true" minOccurs="0">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element ref="DATA_VERSION"/>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                      </xs:sequence>
                </xs:complexType>
          </xs:element>
       </xs:sequence>
     </xs:complexType>
    </xs:element>
    <xs:element name="DATA_VERSION" nillable="true">
        <xs:complexType>
            <xs:attribute name="_Name" type="xs:string" use="required"/>
            <xs:attribute name="_Number" type="xs:string" use="required"/>
        </xs:complexType>
    </xs:element>
</xs:schema>
'

Answer 3

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>')

Answer 4

Now, execute

SELECT test.query('declare namespace test="http://mrgdev.local/test/";//test:LOAN')
FROM MyTest

You'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?

Answer 5

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.

Answer 6

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?