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:

<Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

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 ('<MAIN>
<Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <event>53</event>
  <myfield>a</myfield>
</Secondary>
<Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <event>56</event>
  <myfield>a</myfield>
</Secondary>
</MAIN>
')

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:

<MAIN>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>53</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield>different string o tet</myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>

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

<MAIN>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>53</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield></myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>

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)

What is your final goal? The part, where you write *What I want, at the end* seems to be just the `` opening tag. But this alone would not be well formed XML... — Shnugo, Jul 01 '20 at 09:38
@Shnugo yes, that's specifically what I'm trying to get out of this. The actual end goal involves filtering the fields in some of subnodes based on other fields (if the document contains subnodes with these particular values, rewrite other subnodes), but all that works - all I need is the secondary with the xsi/xsd, I'll slam them together using FOR XML PATH to create the XML, and go on my way. — mbourgon, Jul 01 '20 at 13:35
Well, it would really help if you'd provide a [mcve]. Some sample as input and the expected output. I must admit that I have no idea, what you really want. Creating opening tags? That was not XML but just something that looks like XML... Manipulating an existing XML? You don't show enough to help you with this... To be honest this sounds like a XY-problem... — Shnugo, Jul 01 '20 at 14:33
@Shnugo added. Hopefully it explains why I was trying to keep the original request "simpler". — mbourgon, Jul 01 '20 at 16:58
Looking at your code I see `.exist('/MAIN/EventData[event="56"]')` which points to a different XML, no `` in the sample above... — Shnugo, Jul 01 '20 at 17:30
@Shnugo In this case, I'm just an idiot - was substituting out bits from my actual code as opposed to the example here. Looking at the answer now. — mbourgon, Jul 01 '20 at 17:59

score 1 · Accepted Answer · answered Jul 01 '20 at 17:25

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 
 ('<MAIN> <!-- event 53 exists -->
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>53</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield>different string o tet</myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>')

,(N'<MAIN> <!-- no event 53 here!!! -->
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>50</event>
      <myfield>string o text</myfield>
      <myfield2>some other string</myfield2>
    </Secondary>
    <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <event>56</event>
      <myfield>different string o tet</myfield>
      <myfield2>and some other other strings</myfield2>
    </Secondary>
    </MAIN>');

--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:

<MAIN>
  <!-- event 53 exists -->
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>53</event>
    <myfield>string o text</myfield>
    <myfield2>some other string</myfield2>
  </Secondary>
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>56</event>
    <myfield>Some replacement</myfield>
    <myfield2>and some other other strings</myfield2>
  </Secondary>
</MAIN>

... and the second like this

<MAIN>
  <!-- no event 53 here!!! -->
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>50</event>
    <myfield>string o text</myfield>
    <myfield2>some other string</myfield2>
  </Secondary>
  <Secondary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <event>56</event>
    <myfield>different string o tet</myfield>
    <myfield2>and some other other strings</myfield2>
  </Secondary>
</MAIN>

The idea in short:

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

Is there a way to do it without updating the temp table? When I just turn it into a straight `SELECT switch_xml.modify ... from @xml`, I get "Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context." — mbourgon, Jul 01 '20 at 18:54
Just figured that part out - .modify only works directly, not via select ([link to SO about this](https://stackoverflow.com/questions/5087950/sql-xquery-how-to-replace-text-in-update-query)), and since my data pull can be 3gb, pretty sure tossing it into a table variable/temp table is going to give me a major perf hit. Hmmm. i'll have to see what the cost is. — mbourgon, Jul 01 '20 at 19:00
@mbourgon not sure about your issue... You can use modify in SET against a xml typed variable too — Shnugo, Jul 01 '20 at 19:05
What we’re doing is a select from one server to another server, and we want to leave the original data. However, I think we can modify it to run the modify after we pull but before we tell it to process. Thanks! — mbourgon, Jul 01 '20 at 19:32
@mbourgon In this case it might speed up things, if you 1) copy the XMLs to the target and then use `UPDATE` with `.modify()` together with a `WHERE`-clause testing with `.exist()`. Especially if many rows do not need the update, this might help a lot... — Shnugo, Jul 02 '20 at 06:58

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

1 Answers1