question

VladimirMoldovanenko-3700 avatar image
0 Votes"
VladimirMoldovanenko-3700 asked VladimirMoldovanenko-3700 answered

How to overcome Msg 2389, Level 16, State 1, Line 126 XQuery [value()]: 'value()' error in xml sample provided

Thank you for taking time to read my post and for your expertise in this matter.
I appreciate it.

I am struggling to overcome this error when parsing typed xml attribute/value into a text
See below

 IF EXISTS
 (
     SELECT *
     FROM sys.xml_schema_collections c
     JOIN sys.schemas s ON s.schema_id = c.schema_id
     WHERE s.[name] = N'dbo'
     AND c.[name] = N'MachineEventsSimple'
 )
     DROP XML SCHEMA COLLECTION dbo.MachineEventsSimple
 GO
 DECLARE @MachineEventsSimple nvarchar(max)
    
 -- Assign the schema
 SET @MachineEventsSimple =
 N'<?xml version="1.0" encoding="utf-16"?>
 <xsd:schema xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" attributeFormDefault="unqualified" elementFormDefault="qualified" version="12.5.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:import schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
   <xsd:element name="MachineEvent">
     <xsd:complexType>
       <xsd:sequence>
         <xsd:choice minOccurs="1" maxOccurs="1">
           <xsd:element minOccurs="0" maxOccurs="1" name="SCAN">
             <xsd:complexType>
               <xsd:attribute name="Scan" use="required">
                 <xsd:simpleType>
                   <xsd:restriction base="sqltypes:nvarchar">
                     <xsd:minLength value="1" />
                     <xsd:maxLength value="28" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:attribute>
               <xsd:attribute name="cptCode" use="optional">
                 <xsd:simpleType>
                   <xsd:restriction base="sqltypes:nvarchar">
                     <xsd:minLength value="1" />
                     <xsd:maxLength value="25" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:attribute>
               <xsd:attribute name="venCode" use="optional">
                 <xsd:simpleType>
                   <xsd:restriction base="sqltypes:nvarchar">
                     <xsd:minLength value="1" />
                     <xsd:maxLength value="25" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:attribute>
               <xsd:attribute name="LocationPath" use="optional">
                 <xsd:simpleType>
                   <xsd:restriction base="sqltypes:nvarchar">
                     <xsd:minLength value="1" />
                     <xsd:maxLength value="100" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:attribute>
             </xsd:complexType>
           </xsd:element>
           <xsd:element minOccurs="0" maxOccurs="1" name="ALERT">
             <xsd:complexType>
               <xsd:attribute name="Message" use="required">
                 <xsd:simpleType>
                   <xsd:restriction base="sqltypes:nvarchar">
                     <xsd:minLength value="1" />
                     <xsd:maxLength value="25" />
                   </xsd:restriction>
                 </xsd:simpleType>
               </xsd:attribute>
             </xsd:complexType>
           </xsd:element>
           <xsd:element minOccurs="0" maxOccurs="1" name="INFO">
             <xsd:complexType>
               <xsd:anyAttribute processContents="skip" />
             </xsd:complexType>
           </xsd:element>
         </xsd:choice>
         <xsd:element minOccurs="0" maxOccurs="1" name="DATA">
           <xsd:complexType>
             <xsd:sequence>
               <xsd:any minOccurs="0" processContents="skip" />
             </xsd:sequence>
             <xsd:anyAttribute processContents="skip" />
           </xsd:complexType>
         </xsd:element>
       </xsd:sequence>
       <xsd:attribute name="mcnCode" use="required">
         <xsd:simpleType>
           <xsd:restriction base="sqltypes:nvarchar">
             <xsd:minLength value="1" />
             <xsd:maxLength value="25" />
           </xsd:restriction>
         </xsd:simpleType>
       </xsd:attribute>
       <xsd:attribute name="CreatedOn" type="sqltypes:datetimeoffset" use="required" />
     </xsd:complexType>
   </xsd:element>
 </xsd:schema>';
    
 IF NOT EXISTS
 (
     SELECT *
     FROM sys.xml_schema_collections c
     JOIN sys.schemas s ON s.schema_id = c.schema_id
     WHERE s.[name] = N'dbo'
     AND c.[name] = N'MachineEventsSimple'
 )
     CREATE XML SCHEMA COLLECTION dbo.MachineEventsSimple AS @MachineEventsSimple
 GO
    
    
 DECLARE @MachineEvent xml
    
 -- if the below line is enabled and the above line commented out, then I get an error  
    
 --DECLARE @MachineEvent xml (DOCUMENT dbo.MachineEventsSimple)
    
 /*
 Msg 2389, Level 16, State 1, Line 126
 XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type '<anonymous> | sqltypes{http://schemas.microsoft.com/sqlserver/2004/sqltypes}:datetimeoffset | xs:boolean | xs:QName | xs:anyURI *'
    
 */
    
 -- How to make the below attribute/value retrieval code work with schema bound typed variable, i.e. overcome this error? a value should be a text type
    
 SET @MachineEvent = -- SCAN sample
 N'<MachineEvent mcnCode="Holzma" CreatedOn="2021-08-05T14:33:59.484231-05:00">
     <SCAN Scan="ICN114515" cptCode="Cutting" venCode="AdzicF" LocationPath="??" />
     <DATA Temperature="24" WorkTime="5321" Cycles="135"/>
 </MachineEvent>'
    
    
 SELECT
     mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
     ,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
 FROM @MachineEvent.nodes('MachineEvent/@*' ) mi(c)
    
 --UNION ALL
    
 --SELECT
 --    mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
 --    ,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
 --FROM @MachineEvent.nodes('MachineEvent/*[1]/@*' ) mi(c)
    
 --UNION ALL
                
 --SELECT
 --    mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
 --    ,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
 --FROM @MachineEvent.nodes('MachineEvent/DATA/@*' ) mi(c)

Thanks
Vladimir

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @VladimirMoldovanenko-3700,

It seems like a bug in SQL Server XQuery.

Please try the following workaround.

SQL

 SELECT mcnepCode = c.value('local-name(.)', 'nvarchar(50)' )
  , mcnepValue = c.query('data(.)').value('.', 'nvarchar(4000)')
  --, mcnepValue = c.value( '.', 'nvarchar(4000)')
 FROM @MachineEvent.nodes('/MachineEvent/@*' ) mi(c);


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VladimirMoldovanenko-3700 avatar image
0 Votes"
VladimirMoldovanenko-3700 answered

@YitzhakKhabinsky-0887 ,

Thank you!

Your workaround works.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.