SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Querying XML from SSMS


Querying XML from SSMS

Author
Message
janvan
janvan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
gbritton1
gbritton1
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 874
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

hunchback
hunchback
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 639
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);





janvan
janvan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
Thanks to both of you. That's exactly what I was looking for. :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search