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)
