Select @XML.nodes with Name spaces Help Please error msg 2209 XQuery nodes

  • Hi All,

    I have spent a few hours after revisiting this serval times and have never come up with a working solution for Nodes which contain a space in name.  the node I want is called   Item param

    error Msg 2209, Level 16, State 1, Line 18

    XQuery [nodes()]: Syntax error near 'Item'

     

    This sample I have working if the node is a whole word with out spaces

    Please see my sample test code which I want to extract  the node in red and value

    <Item param="REFERENCE_NBR" value="254567" label="" type="System.String" reportControlType="None" />

    the current sample code works with Node DefaultRpx

    your help much appreciated

    regards Peter

     

    --working below but if you enter a node with space in Name its not working
    declare @temp table(DefaultRpx nvarchar(100),[Item param] nvarchar(100))
    declare @XML xml
    set @XML =
    '<SelectionCriteria>
    <DefaultRpx value="TEST.RPX" />
    <ServiceType value="Report" />
    <DocumentType value="Report" />
    <Print value="Inbox" />
    <DynamicProcedureName value="" />
    <Item param="REFERENCE_NBR" value="254567" label="" type="System.String" reportControlType="None" />
    </SelectionCriteria>'


    insert into @temp
    select T.c.value('@Itemparam','nchar(5)'),
    T.c.value('@value', 'nvarchar(40)')
    from @XML.nodes('/SelectionCriteria/DefaultRpx') AS T(c)
    select * from @temp
  • Does this help?

    SELECT ItemParam = T.c.value('Item[1]/@param', 'nvarchar(50)')
    ,ItemValue = T.c.value('Item[1]/@value', 'nchar(10)')
    ,DefaultRpx = T.c.value('DefaultRpx[1]/@value', 'nvarchar(40)')
    FROM @XML.nodes('/SelectionCriteria') T(c);

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil ,

    First thank you for your reply and yes it works thankyou..... you are a champion!!

    Phil  what is the best way to apply this logic to a sql select query on a table with normal fields and  the xml field SelectionCriteria ?

    select Field, Field2 , SelectionCriteria  from table

    also now I assume the chars in red is the way you deal with spaces in names   Item[1]/@param?

    best regard's Peter

    • This reply was modified 1 week, 5 days ago by  hagar333.
  • hagar333 wrote:

    Hi Phil ,

    First thank you for your reply and yes it works thankyou..... you are a champion!!

    Phil  what is the best way to apply this logic to a sql select query on a table with normal fields and  the xml field SelectionCriteria ?

    select Field, Field2 , SelectionCriteria  from table

    also now I assume the chars in red is the way you deal with spaces in names   Item[1]/@param?

    best regard's Peter

    I think you are misunderstanding what the XML is telling you.

    'Item param' is not a name with spaces. Instead, Item is the node name and param is an attribute (or whatever the XML terminology for this is) of Item.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • To answer your other question, CROSS APPLY is one way:

    DROP TABLE IF EXISTS #SomeXML;

    CREATE TABLE #SomeXML
    (
    Col1 VARCHAR(50) NOT NULL
    ,Col2 VARCHAR(50) NOT NULL
    ,ColXML XML NOT NULL
    );

    INSERT #SomeXML
    (
    Col1
    ,Col2
    ,ColXML
    )
    VALUES
    ('c1', 'c2'
    ,'<SelectionCriteria>
    <DefaultRpx value="TEST.RPX" />
    <ServiceType value="Report" />
    <DocumentType value="Report" />
    <Print value="Inbox" />
    <DynamicProcedureName value="" />
    <Item param="REFERENCE_NBR" value="254567" label="" type="System.String" reportControlType="None" />
    </SelectionCriteria>')
    ,('c1', 'c2'
    ,'<SelectionCriteria>
    <DefaultRpx value="TEST2.RPX" />
    <ServiceType value="Report" />
    <DocumentType value="Report" />
    <Print value="Inbox" />
    <DynamicProcedureName value="" />
    <Item param="REFERENCE_NBR" value="987654" label="" type="System.String" reportControlType="None" />
    </SelectionCriteria>');

    SELECT sx.Col1
    ,sx.Col2
    ,sx.ColXML
    ,c1.ItemParam
    ,c1.ItemValue
    ,c1.DefaultRpx
    FROM #SomeXML sx
    CROSS APPLY
    (
    SELECT ItemParam = T.c.value('Item[1]/@param', 'nvarchar(50)')
    ,ItemValue = T.c.value('Item[1]/@value', 'nchar(10)')
    ,DefaultRpx = T.c.value('DefaultRpx[1]/@value', 'nvarchar(40)')
    FROM sx.ColXML.nodes('/SelectionCriteria') T(c)
    ) c1;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Phil ,

    Thank you  for both replies I have been working on this and trying to get my head around how this works as you mentioned Node names and attributes hence my delayed reply.

    I have this up and running with a where statement as well in xml

    and understand now how the nodes work and values to query

    Thankyou for your patients and time Phil

    regards Peter

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply