SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query XML Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

janvan
Starting Member

1 Posts

Posted - 05/05/2014 :  11:07:57  Show Profile  Reply with Quote
Hi,

I have an xml document that (for this example) I've simplified to look like this:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='ItemCode' rs:number='1' rs:writeunknown='true' rs:basecatalog='Vehicles' rs:basetable='vehicle_option'
rs:basecolumn='ItemCode' rs:keycolumn='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='8' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='OptionPrice_Code' rs:number='2' rs:writeunknown='true' rs:basecatalog='Vehicles' rs:basetable='vehicle_option'
rs:basecolumn='OptionPrice_Code'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='OptionName_Code' rs:number='3' rs:writeunknown='true' rs:basecatalog='Vehicles' rs:basetable='vehicle_option'
rs:basecolumn='OptionName_Code' rs:keycolumn='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row ItemCode='02077400' OptionPrice_Code='1' OptionName_Code='1006'/>
<z:row ItemCode='02077400' OptionPrice_Code='5' OptionName_Code='1009'/>
<z:row ItemCode='02077500' OptionPrice_Code='1' OptionName_Code='1006'/>
<z:row ItemCode='02077500' OptionPrice_Code='5' OptionName_Code='1009'/>
<z:row ItemCode='02078100' OptionPrice_Code='5' OptionName_Code='49'/>
</rs:data>
</xml>

When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:

declare @x xml

select @x = P
from openrowset (bulk 'E:\VehicleOption0514.xml', single_blob) as Products(P)

declare @hdoc int

exec sp_xml_preparedocument @hdoc output, @x

select
Tbl.Col.value('@ItemCode', 'varchar(8)'),
Tbl.Col.value('@OptionPrice_Code', 'bigint'),
Tbl.Col.value('@OptionName_Code', 'bigint')
from
@x.nodes('//row') Tbl(Col)

exec sp_xml_removedocument @hdoc


Please help or point me in the right direction. This is the first time I've had to query xml from sql.

Thanks,
J

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/05/2014 :  11:23:33  Show Profile  Reply with Quote
You should specify the namespace with the ;with xmlnamespaces construct. So, it would be:
;WITH XMLNAMESPACES ( DEFAULT '#RowsetSchema')
select 
Tbl.Col.value('@ItemCode', 'varchar(8)'),
Tbl.Col.value('@OptionPrice_Code', 'bigint'),
Tbl.Col.value('@OptionName_Code', 'bigint')
from 
@x.nodes('//row') Tbl(Col)
You don't necessarily have to use that default, but if you don't, you will need to indicate the namespace in the query itself. See here for details: http://technet.microsoft.com/en-us/library/ms177400.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000