Interview Question in Sql Server 2000
Interview Question :: How do I use OPENXML with mixed/multiple elements and attributes |
How do I use OPENXML in SQL Server 2000 with some elements that are repeated and some elements only contain attributes but others don't?
The problematic part of my XML looks like this:
<ResultSet>
<Result>
<hoursSaturday>Noon to 6:00 pm</hoursSaturday>
<hoursSunday>Closed to Closed</hoursSunday>
<image url="7777.png"/>
<image url="149.png"/>
<field title="Directions" value="From Aber "/>
<field title="Accreditations" value="We belong"/>
</Results>
</ResultSet>
When I use: SELECT *
FROM openxml(@hdoc, '//spResultSet/result', 2)
WITH ([image] varchar(300) 'image/@url',
[image] varchar(300) 'image/@url',
field varchar(255) 'field/@value',
field varchar(255) 'field/@value',
whatever the last @url is gets put into every [image] column, same as the last @Value gets put into every field column.
If I use a 0 or 1 for my flags in my OPENXML statement, other fields aren't read right. I think I need help with the XPATH |
|
|
|
Loading ...