Tuesday, March 27, 2012

Can I get both Attributes and Elements values in same OpenXML query ?

As an input parameter for stored procedure I have the following XML, its values are contained in both attributes and elements. For attributes we have to use flag = 1, and for attributes 2. Is there any flag or technique that we can retrieve both ?

Here is my script:

declare
@.xml xml,
@.handle int

set @.xml =
'
<Sortable>
<Field ord="1" type="asc">LastName</Field>
<Field ord="2" type="desc">CreateDate</Field>
<Field ord="3" type="asc">ProspectNum</Field>
</Sortable>
'

exec sp_xml_preparedocument @.handle output, @.xml


select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) --'./Field'
)

exec sp_xml_removedocument @.handle

I exepect it to return the foillowing result set:

ord type Field
-- - --
1 asc LastName
2 desc CreateDate
3 asc ProspectNum

Obviously it returns NULL for my 'Field' column.

Thanks

Either of these

select
ord,
type,
Field
from openxml(@.handle, '/Sortable/Field', 1)
with
(
ord int,
type varchar(4),
Field varchar(20) '.'
)


select r.value('@.ord','int') as ord,
r.value('@.type','varchar(4)') as type,
r.value('.','varchar(20)') as Field
from @.xml.nodes('/Sortable/Field') as D(r)

No comments:

Post a Comment