3

This is a variation/combination of these two questions, but I think I've done as they described:

Is there a way to get the name of a node in a SQL Server 2016 T-SQL query?

I want to do this: 

select 
    trcid,
    trcXML.value(
        'local-name(//*[local-name()="after"][1]/*[1])',  
        'varchar(32)'
    ) as XmlTableName,
    trcXml, 
    trcGUID, 
    trcCorrID 
from trace 
order by trcId desc

I have explicitly included the [1] after each item in the hierarchy.

Error: XQuery [trace.trcXML.value()]: 'local-name()' requires a singleton (or empty sequence), found operand of type 'element(*,xdt:untyped) *'

Sample data: 

<s1:DB2Request xmlns:s1="XYZDB2">
  <s1:sync>
    <s1:after identityInsert="false">
      <s1:PG204AT5>
        <s1:ISA06>1975111</s1:ISA06>
           etc... 
      </s1:PG204AT5>
    </s1:after>
  </s1:sync>
</s1:DB2Request>

I want the table name (this is a BizTalk "update-gram") which is between the "after" and the ISA06 elements, in this case: PG204AT5.

Tested in xpathtester.com: local-name(//*[local-name()="after"][1]/* [1]) - for some reason when I save it the XPATH gets changed: http://www.xpathtester.com/xpath/2bd602d8fc7aee0484de14bf93f71ef2

GMB
  • 216,147
  • 25
  • 84
  • 135
NealWalters
  • 17,197
  • 42
  • 141
  • 251
  • What do you mean by Node? Is it Server name? SELECT @@servername AS 'Server Name' ,@@servicename AS 'Instance Name' ,DB_NAME() AS 'Database Name' ,HOST_NAME() AS 'Host Name' – CR241 Jan 25 '20 at 00:27

3 Answers3

2

One more set of parenthesis gives you a singleton as required:

DECLARE @t TABLE(s XML);
INSERT INTO @t(s)VALUES(N'<s1:DB2Request xmlns:s1="XYZDB2">
  <s1:sync>
    <s1:after identityInsert="false">
      <s1:PG204AT5>
        <s1:ISA06>1975111</s1:ISA06>
           <somethingsomething/>
      </s1:PG204AT5>
    </s1:after>
  </s1:sync>
</s1:DB2Request>');

select 
    s.value(
        'local-name((//*[local-name()="after"][1]/*)[1])',  
        'varchar(32)'
    ) as XmlTableName
from @t;

Results in:

XmlTableName
PG204AT5
TT.
  • 15,774
  • 6
  • 47
  • 88
2

Remember that the SQL Server engine, unlike most XQuery implementations, does "pessimistic static typing" - it assumes that if things can go wrong, they will go wrong.

The expression //*[local-name()="after"][1]/* [1] is capable of returning more than one element - for example it will do so with this input

<x>
  <y>
    <after>
       <z/>
    </after>
  </y>
  <y>
    <after>
       <z/>
    </after>
  </y>
</x>

so the XQuery compiler infers a static type of element()*, which doesn't meet the requirement.

It's probably enough to simply do (//*[local-name()="after"]/*)[1].

Personally, I always thought pessimistic static typing was a really poor design choice, but some of the early XQuery implementors including Microsoft were very keen on it.

Michael Kay
  • 156,231
  • 11
  • 92
  • 164
  • I'm fuzzy on why putting the first [1] without the parentheses doesn't act as a subscript. Is the parser doing something else with it or ignoring it totally? – NealWalters Jan 28 '20 at 17:03
  • 1
    `//X[1]` parses as `/descendant-or-self::node()/(child::X[1])` which returns every X that is the first child of its parent. By contrast, `(//X)[1] finds all the X elements in the document and then selects the first. – Michael Kay Jan 28 '20 at 18:55
2

Or even simpler solution by asking explicitly first child node name of the <s1:after> element.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE(xmldata XML);
INSERT INTO @tbl(xmldata)
VALUES(N'<s1:DB2Request xmlns:s1="XYZDB2">
  <s1:sync>
    <s1:after identityInsert="false">
      <s1:PG204AT5>
        <s1:ISA06>1975111</s1:ISA06>
           <somethingsomething/>
      </s1:PG204AT5>
    </s1:after>
  </s1:sync>
</s1:DB2Request>');
-- DDL and sample data population, end

;WITH XMLNAMESPACES (DEFAULT 'XYZDB2')
SELECT xmldata.value('local-name((/DB2Request/sync/after/child::node())[1])','VARCHAR(50)') as XmlTableName
FROM @tbl;

Output

+--------------+
| XmlTableName |
+--------------+
| PG204AT5     |
+--------------+
TT.
  • 15,774
  • 6
  • 47
  • 88
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21