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: 246960
More actions
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);
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 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 2 years, 5 months ago by hagar333.

Phil Parkin SSC Guru Points: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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