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

Question

Post reply

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

hagar333

SSC Enthusiast

Points: 184
More actions
June 23, 2022 at 3:12 am
Go to Answer
#4053658
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
```

Phil Parkin

SSC Guru

Points: 247180

June 23, 2022 at 8:00 am

Answer

#4053732

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);

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

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

hagar333 SSC Enthusiast Points: 184 More actions · Answer 1

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 3 years, 2 months ago by hagar333.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

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;

hagar333 SSC Enthusiast Points: 184 More actions · Answer 4

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