0

Hi I've been trying to get all the XML tags values, But I am having issues, for instance I am able to get all the tag values if I manually add the index as [1],[2],.... but I want all the tag values with all indices, 

    <Menu>
      <Titles>
        <Title LCID="1033" Text="ISV" />
      </Titles>
      <MenuItem Url="http://www.microsoft.com">
        <Titles>
          <Title LCID="1033" Text="New Window" />
        </Titles>
      </MenuItem>
      <MenuSpacer />
      <!-- A horizontal drop down menu spacer -->
      <SubMenu>
        <Titles>
          <Title LCID="1033" Text="ISV Tests" />
        </Titles>
        <MenuItem Url="http://www.microsoft.com" WinMode="2">
          <Titles>
            <Title LCID="1033" Text="Modeless Dialog" />
          </Titles>
        </MenuItem>
        <MenuSpacer />
        <MenuItem Url="http://www.microsoft.com" WinMode="1">
          <Titles>
            <Title LCID="1033" Text="Modal Dialog" />
          </Titles>
        </MenuItem>
        <MenuSpacer />
        <MenuItem JavaScript="alert('test');">
          <Titles>
            <Title LCID="1033" Text="Custom JavaScript" />
          </Titles>
        </MenuItem>
      </SubMenu>
    </Menu>
    <Menu>
      <Titles>
        <Title LCID="1033" Text="c360 Tools" />
      </Titles>

My Xquery works if I give Indices Manually
e.p.value('(Menu/Titles/Title/@Text)[1]','varchar(max)')
gives me "ISV", but I want all tag values, Any Help

The full query:

WITH ConvOrg As
(
    SELECT CAST(ConfigXML As xml) As x FROM IsvConfig
)
SELECT 
    e.p.value('(@Text)','varchar(max)') As Menu, 
    e.p.value('(../../../../../@Text)[1]','varchar(max)')   As SubMenu 
FROM    ConvOrg 
CROSS APPLY 
        ConvOrg.x.nodes('Menu/SubMenu/MenuItem/Titles/Title') As e(p)
hunch_hunch
  • 2,283
  • 1
  • 21
  • 26
Vijay Bonam
  • 1
  • 1
    Show us the full query of what you currently have. To fix this you have to change more than one thing in your query, so we need to see all of it. – RBarryYoung Apr 23 '15 at 14:26
  • with ConvOrg as( select CAST(ConfigXML as xml) as x from IsvConfig ) SELECT e.p.value('(@Text)','varchar(max)')as Menu, e.p.value('(../../../../../@Text)[1]','varchar(max)')as SubMenu from ConvOrg cross apply ConvOrg.x.nodes('Menu/SubMenu/MenuItem/Titles/Title') as e(p) I am dynamically taking XML from db – Vijay Bonam Apr 23 '15 at 15:42
  • OK, that helps. You can certainly return all of the `Text=` attribute values in the [Menu] column. However, it is unclear what you are trying to do with the [SubMenu] column ... ? You clearly want the text attribute value from some ancestor of the current item, but given that the [Menu] values will come form many different levels, which ancestor value do you want there? – RBarryYoung Apr 24 '15 at 15:53

0 Answers0