none
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?
    Monday, September 25, 2006 7:49 PM

All replies

  • <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>
    Monday, September 25, 2006 9:33 PM
  • 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>
    '

    Monday, September 25, 2006 9:54 PM
  • 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>')
    Monday, September 25, 2006 9:55 PM
  • 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?
    Monday, September 25, 2006 10:01 PM
  • 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.
    Monday, September 25, 2006 10:09 PM
  • 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?
    Tuesday, September 26, 2006 2:05 PM