XPath/XQuery - getting the field with namespace attributes out as a value/query?

Question

I have XML that looks like the below code. My end goal involves changing some nodes and stripping out others using nodes, exist, value, and then recreating this new XML using FOR XML PATH - which is all working fine.

However, I cannot figure out how to get back just the row/attributes for "Secondary", specifically the xmlns:xsi and xmlns:xsd).

For the below example, how do I just get the namespace attributes inside Secondary, so that I can combine it with the tweaked event/myfield/etc using FOR XML PATH? Or do I need to write a FLWOR to do this? (And if so, any advice on at least this part?)

What I want, at the end:

I can then combine it with the other fields I'm mucking with using FOR PATH XML and feed it downstream. If it's any easier, could you pull out the xmlns:xsi and xmlns:xsd with "value" and concatenate it so that it looks the same?

DECLARE @xml TABLE (id int IDENTITY, switch_xml XML)
INSERT INTO @xml (switch_xml)
VALUES ('

  53
  a


  56
  a


')

SELECT 
--- ???? getting the Secondary here
Ev.Dat.query('event') 
FROM @xml X OUTER APPLY switch_xml.nodes('/MAIN/Secondary') AS Ev(Dat)

More reproducible example:


    
      53
      string o text
      some other string
    
    
      56
      different string o tet
      and some other other strings

requested returned - because there's a event 53, strip out just myfield on the event 56. If there wasn't an event 53 node, you'd leave the 56 alone


    
      53
      string o text
      some other string
    
    
      56
      
      and some other other strings

my example of that query, which doesn't handle the namespace: (writing it now - this gets the nodes, just need to properly wrapper it to combine the nodes into one MAIN)

SELECT 
--- ???? getting the Secondary here
CONVERT(XML,(SELECT Ev.Dat.query('event') ,
CASE WHEN switch_xml.exist('/MAIN/EventData[event="56"]') = 1 AND ev.dat.value('(event)[1]','int') IN (53) THEN ev.dat.query('(myfield)[1]')
WHEN switch_xml.exist('/MAIN/EventData[event="56"]') = 0 AND ev.dat.value('(event)[1]','int') IN (53) THEN ev.dat.query('(myfield)[1]') else '' END
,Ev.Dat.query('myfield2') FOR XML PATH('Secondary')))  AS newxml
FROM @xml X OUTER APPLY switch_xml.nodes('/MAIN/Secondary') AS Ev(Dat)

Gottfried Lesigang · Accepted Answer

Thanks for adding a sample to your XML.

Might be that this does not cover all your needs, but I think you might walk this route:

Attention: I'm assuming, that there is just one occurance of any event id per XML.

Your sample mockup table. I added two rows, one having event 53 and one not.

DECLARE @xml TABLE (id int IDENTITY, switch_xml XML)
INSERT INTO @xml (switch_xml)
VALUES 
 (' 
    
      53
      string o text
      some other string
    
    
      56
      different string o tet
      and some other other strings
    
    ')

,(N' 
    
      50
      string o text
      some other string
    
    
      56
      different string o tet
      and some other other strings
    
    ');

--using parameters makes life easier

DECLARE @SearchForEvent INT = 53;
DECLARE @ChangeThisEvent INT = 56;
DECLARE @ReplaceWith VARCHAR(100)='Some replacement';

--We can use .modify() to update the needed value

UPDATE @xml SET switch_xml.modify('replace value of (/MAIN[Secondary[event=sql:variable("@SearchForEvent")]]
                                                     /Secondary[event=sql:variable("@ChangeThisEvent")]
                                                     /myfield
                                                     /text())[1] with sql:variable("@ReplaceWith")');

--Check the result

SELECT * FROM @xml;

The first XML looks like this:


  
  
    53
    string o text
    some other string
  
  
    56
    Some replacement
    and some other other strings

... and the second like this


  
  
    50
    string o text
    some other string
  
  
    56
    different string o tet
    and some other other strings

The idea in short:

We tell .modify() via XPath what to change
The Xpath will dive into
if there is a , which has a element with the value as given (=53)
if this holds true we dive below
into , searching for an element where has the other given value (=56)
If found, we can replace this with the third given value.

XPath/XQuery - getting the field with namespace attributes out as a value/query?

Answers (1)

Related Questions