SQL SERVER : How to make query from XML file

I have two xml file like this:

<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Truck_Number_Non_Barcode>MNKSJJHDHH88728</Truck_Number_Non_Barcode>
<VIN>
  <Non_Barcode>xyz</Non_Barcode>
  <ODO>1425788</ODO> 
  <Defect>
    <Code>33J</Code>
</Defect> 
</VIN>
</inputs>
</instance>

And

<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Number_Non_Barcode>mnbcdsddsd3455</Number_Non_Barcode>
<VIN>
  <Non_Barcode>xyz</Non_Barcode>
  <ODO>1425788</ODO>      
</VIN>
</inputs>
</instance>

Below is my sqlquery for parsing xml file:

declare @xmldata xml
set @xmldata ='
<?xml version="1.0"?>
<instance xmlns="http://www.ubrea.com/xforms/88668970-6edb-0131-28e9-22000a1cda92" xmlns:tm="http://www.ubrea.com/xforms" >
<inputs>
<Number_Non_Barcode>mnbcdsddsd3455</Number_Non_Barcode>
<VIN>
  <Non_Barcode>xyz</Non_Barcode>
  <ODO>1425788</ODO>      
</VIN>
</inputs>
</instance>'
declare @sql nvarchar(max)
declare @xmlns varchar(max)
set @xmlns=''''+SUBSTRING(cast(@xmldata as varchar(max)),CHARINDEX('http://www.ubrea.com/xforms/',cast(@xmldata as varchar(max)),1),CHARINDEX('" xmlns:dm',cast(@xmldata as varchar(max)),1)-18)+''''
set @sql='
declare @xmldata xml
set @xmldata = '''+cast(@xmldata as varchar(max))+'''
begin try
;WITH XMLNAMESPACES 
(
    DEFAULT ' + @xmlns +
     ','+'''http://www.ubrea.com/xforms''' + ' as fm 
)
select
    Number_Non_Barcode, Non_Barcode, ODO, Code
from (
select          
    x.c.value(''(../../Number_Non_Barcode)[1]'', ''varchar(100)'') as Number_Non_Barcode,       
    x.c.value(''(../Non_Barcode)[1]'', ''varchar(100)'') as Non_Barcode,
    x.c.value(''(../ODO)[1]'', ''varchar(100)'') as ODO,
    x.c.value(''(Code)[1]'', ''varchar(100)'') as Code      
from @xmldata.nodes(''/instance/inputs/VIN/Defect'') x(c)
) x  

end try
begin catch
select ERROR_NUMBER() Code, ERROR_MESSAGE() Message
end catch'
exec sp_executesql @sql;

How to make Query if <Defect> not available and the result should like this:

Number_Non_Barcode  Non_Barcode   ODO         Defect_Code
mnbcdsddsd3455      xyz           1425788      NULL

I'm not real familiar with xml query and can't seem to find an example of how to do it. Any help would be appreciated. Thanks

I guess that your dynamic query is there because the default namespace is unknown to you. Instead you can specify the namespace in the XQuery using *.

Using the parent axis .. can be avoided by shredding the XML using nodes() and cross apply in several steps. Since the Defect node can be missing you have to use outer apply when shredding those nodes.

select I.X.value('(*:Number_Non_Barcode/text())[1]', 'varchar(100)') as Number_Non_Barcode,
       V.X.value('(*:Non_Barcode/text())[1]', 'varchar(100)') as Non_Barcode,
       V.X.value('(*:ODO/text())[1]', 'varchar(100)') as ODO,
       D.X.value('(*:Code/text())[1]', 'varchar(100)') as Code
from @XMLData.nodes('/*:instance/*:inputs') as I(X)
  cross apply I.X.nodes('*:VIN') as V(X)
  outer apply V.X.nodes('*:Defect') as D(X)

SQL Fiddle