Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Querying XML from SSMS Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:05 AM
Points: 2, Visits: 6
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
Post #1567558
Posted Monday, May 5, 2014 10:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 314, Visits: 694
You need to specify the namespaces for the query e.g.

; with xmlnamespaces ('#RowsetSchema' as z)
select
Tbl.Col.value('@ItemCode', 'varchar(8)'),
Tbl.Col.value('@OptionPrice_Code', 'bigint'),
Tbl.Col.value('@OptionName_Code', 'bigint')
from
@x.nodes('//z:row') Tbl(Col)

produces:

02077400	1	1006
02077400 5 1009
02077500 1 1006
02077500 5 1009
02078100 5 49
Post #1567592
Posted Monday, May 5, 2014 2:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:57 AM
Points: 114, Visits: 611
As it was stated in the previous suggestion, you need to declare the namespaces.

DECLARE @x xml = '
<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>';

WITH XMLNAMESPACES (
'urn:schemas-microsoft-com:rowset' AS rs,
'#RowsetSchema' AS z
)
SELECT
T.x.value('@ItemCode[1]', 'varchar(10)') AS ItemCode,
T.x.value('@OptionPrice_Code[1]', 'smallint') AS OptionPrice_Code,
T.x.value('@OptionName_Code[1]', 'smallint') AS OptionName_Code
FROM
@x.nodes('xml/rs:data/z:row') AS T(x);


If you do not really care about the namespace then you could also use a wildcard for the namespace.

SELECT 
T.x.value('@ItemCode[1]', 'varchar(10)') AS ItemCode,
T.x.value('@OptionPrice_Code[1]', 'smallint') AS OptionPrice_Code,
T.x.value('@OptionName_Code[1]', 'smallint') AS OptionName_Code
FROM
@x.nodes('xml/*:data/*:row') AS T(x);
GO
SELECT
T.x.value('@ItemCode[1]', 'varchar(10)') AS ItemCode,
T.x.value('@OptionPrice_Code[1]', 'smallint') AS OptionPrice_Code,
T.x.value('@OptionName_Code[1]', 'smallint') AS OptionName_Code
FROM
@x.nodes('//*:row') AS T(x);




Post #1567703
Posted Tuesday, May 6, 2014 1:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:05 AM
Points: 2, Visits: 6
Thanks to both of you. That's exactly what I was looking for.
Post #1567812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse