none
Xquery

    Question

  • Hello

     

    I have  XML file which name is ENT.XML and my sql code and XML file is below when I execute query to get

    Table SNS000 values but I get error. Can any one help me
    

     

    Msg 2389, Level 16, State 1, Line 45
    XQuery [T.XmlCol.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

     

    DECLARE @xmlDoc XML
    DECLARE @handle INT
    
    CREATE TABLE T (IntCol int, XmlCol xml)
    truncate table T
    
    INSERT INTO T(XmlCol)
    SELECT * FROM OPENROWSET(
      BULK 'C:\WEB\LIVE\ENT.xml',
      SINGLE_BLOB) AS x
    
    select @xmlDoc= cast(XmlCol as XML) from T
    
    select @xmlDoc as XmlDoc
    
     Create TABLE #TEMPENS0
     (
     ENS0TN varchar(30),
     ENS0SN varchar(30),
     ENS0RT varchar(30),
     ENS0RG varchar(30),
     ENS0AN varchar(30),
     ENS0ST varchar(30),
     ENS0SS varchar(30),
     ENS0TRS varchar(30)
     )
     Create Table #TEMPENS1
     ( 
     ENS1TN varchar(30),
     ENS1SN varchar(30),
     ENS1RT varchar(30),
     ENS1RG varchar(30),
     ENS1AN varchar(30),
     ENS1ST varchar(30),
     ENS1SS varchar(30),
     ENS1TRS varchar(30),
     ENS1SSTR varchar(30),
     ENS1TBS varchar(30)
     ) 
    
    
    
    SELECT 
    ENTEGRATION.ref.value('@NAME', 'NVARCHAR (5)') as [NAME],
    ENTEGRATION.ref.value ('ENS0TN', 'NVARCHAR (30)') as ENS0TN,
    ENTEGRATION.ref.value ('ENS0SN', 'NVARCHAR (30)') as ENS0SN,
    ENTEGRATION.ref.value ('ENS0RT', 'NVARCHAR (30)') as ENS0RT,
    ENTEGRATION.ref.value ('ENS0RG', 'NVARCHAR (30)') as ENS0RG,
    ENTEGRATION.ref.value ('ENS0AN', 'NVARCHAR (30)') as ENS0AN,
    ENTEGRATION.ref.value ('ENS0ST', 'NVARCHAR (30)') as ENS0ST,
    ENTEGRATION.ref.value ('ENS0SS', 'NVARCHAR (30)') as ENS0SS,
    ENTEGRATION.ref.value ('ENS0TRS', 'NVARCHAR (15)') as ENS0TRS
    
    
    FROM T CROSS APPLY XmlCol.nodes ('./TABLE[SNS000]/RECORD') ENTEGRATION(ref)
    
                  
    
    drop table #TEMPENS0
    drop table #TEMPENS1
    

    <?xml version="1.0" encoding="iso-8859-9"?><br/><br/><XENTAS DES=""><br/> <TABLE NAME="SNS000"><br/>  <RECORD><br/>   <ENS0TN>00121150066</ENS0TN><br/>   <ENS0SN/><br/>   <ENS0RT/><br/>   <ENS0RG>1510</ENS0RG><br/>   <ENS0AN>01</ENS0AN><br/>   <ENS0ST>20090306</ENS0ST><br/>   <ENS0SS>110003</ENS0SS><br/>   <ENS0TRS>682503</ENS0TRS><br/>  </RECORD><br/> </TABLE><br/> <TABLE NAME="SNS001"><br/>  <RECORD><br/>   <ENS1TN>00121150066</ENS1TN><br/>   <ENS1SN/><br/>   <ENS1RT/><br/>   <ENS1RG>1510</ENS1RG><br/>   <ENS1AN>01</ENS1AN><br/>   <ENS1ST>20090306</ENS1ST><br/>   <ENS1SS>110003</ENS1SS><br/>   <ENS1TRS>682503</ENS1TRS><br/>   <ENS1SSTR>SSTARI11</ENS1SSTR><br/>   <ENS1TBS>AA682503</ENS1TBS><br/>  </RECORD><br/>   <RECORD><br/>   <ENS1TN>004445250066</ENS1TN><br/>   <ENS1SN/><br/>   <ENS1RT/><br/>   <ENS1RG>6510</ENS1RG><br/>   <ENS1AN>01</ENS1AN><br/>   <ENS1ST>20100306</ENS1ST><br/>   <ENS1SS>110003</ENS1SS><br/>   <ENS1TRS>5682503</ENS1TRS><br/>   <ENS1SSTR>SSTARI11</ENS1SSTR><br/>   <ENS1TBS>BA68258</ENS1TBS><br/>  </RECORD><br/></TABLE><br/></XENTAS><br/>
    

    Tuesday, May 31, 2011 8:11 AM

Answers

  • hi,

    there are two things wrong:

    1) a normal XML file must not contain <br /> HTML tags. I assume this is a copy'n'paste error.
    2) your XPath's are not quite exact. You need to extract a singlton using the [1] indexed access. Take a look at the .value examples

    Try this one:

    DECLARE @Xml XML = '<?xml version="1.0" encoding="iso-8859-9"?><XENTAS DES=""> <TABLE NAME="SNS000"> <RECORD> <ENS0TN>00121150066</ENS0TN> <ENS0SN/> <ENS0RT/> <ENS0RG>1510</ENS0RG> <ENS0AN>01</ENS0AN> <ENS0ST>20090306</ENS0ST> <ENS0SS>110003</ENS0SS> <ENS0TRS>682503</ENS0TRS> </RECORD> </TABLE> <TABLE NAME="SNS001"> <RECORD> <ENS1TN>00121150066</ENS1TN> <ENS1SN/> <ENS1RT/> <ENS1RG>1510</ENS1RG> <ENS1AN>01</ENS1AN> <ENS1ST>20090306</ENS1ST> <ENS1SS>110003</ENS1SS> <ENS1TRS>682503</ENS1TRS> <ENS1SSTR>SSTARI11</ENS1SSTR> <ENS1TBS>AA682503</ENS1TBS> </RECORD> <RECORD> <ENS1TN>004445250066</ENS1TN> <ENS1SN/> <ENS1RT/> <ENS1RG>6510</ENS1RG> <ENS1AN>01</ENS1AN> <ENS1ST>20100306</ENS1ST> <ENS1SS>110003</ENS1SS> <ENS1TRS>5682503</ENS1TRS> <ENS1SSTR>SSTARI11</ENS1SSTR> <ENS1TBS>BA68258</ENS1TBS> </RECORD></TABLE></XENTAS>' ;
    
    SELECT ref.value('../@NAME', 'NVARCHAR (5)') AS [NAME] ,
      ref.value('ENS0TN[1]', 'NVARCHAR (30)') AS ENS0TN ,
      ref.value('ENS0SN[1]', 'NVARCHAR (30)') AS ENS0SN ,
      ref.value('ENS0RT[1]', 'NVARCHAR (30)') AS ENS0RT ,
      ref.value('ENS0RG[1]', 'NVARCHAR (30)') AS ENS0RG ,
      ref.value('ENS0AN[1]', 'NVARCHAR (30)') AS ENS0AN ,
      ref.value('ENS0ST[1]', 'NVARCHAR (30)') AS ENS0ST ,
      ref.value('ENS0SS[1]', 'NVARCHAR (30)') AS ENS0SS ,
      ref.value('ENS0TRS[1]', 'NVARCHAR (15)') AS ENS0TRS
    FROM @Xml.nodes('./XENTAS/TABLE/RECORD') ENTEGRATION ( ref )

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

    Tuesday, May 31, 2011 8:22 AM

All replies

  • hi,

    there are two things wrong:

    1) a normal XML file must not contain <br /> HTML tags. I assume this is a copy'n'paste error.
    2) your XPath's are not quite exact. You need to extract a singlton using the [1] indexed access. Take a look at the .value examples

    Try this one:

    DECLARE @Xml XML = '<?xml version="1.0" encoding="iso-8859-9"?><XENTAS DES=""> <TABLE NAME="SNS000"> <RECORD> <ENS0TN>00121150066</ENS0TN> <ENS0SN/> <ENS0RT/> <ENS0RG>1510</ENS0RG> <ENS0AN>01</ENS0AN> <ENS0ST>20090306</ENS0ST> <ENS0SS>110003</ENS0SS> <ENS0TRS>682503</ENS0TRS> </RECORD> </TABLE> <TABLE NAME="SNS001"> <RECORD> <ENS1TN>00121150066</ENS1TN> <ENS1SN/> <ENS1RT/> <ENS1RG>1510</ENS1RG> <ENS1AN>01</ENS1AN> <ENS1ST>20090306</ENS1ST> <ENS1SS>110003</ENS1SS> <ENS1TRS>682503</ENS1TRS> <ENS1SSTR>SSTARI11</ENS1SSTR> <ENS1TBS>AA682503</ENS1TBS> </RECORD> <RECORD> <ENS1TN>004445250066</ENS1TN> <ENS1SN/> <ENS1RT/> <ENS1RG>6510</ENS1RG> <ENS1AN>01</ENS1AN> <ENS1ST>20100306</ENS1ST> <ENS1SS>110003</ENS1SS> <ENS1TRS>5682503</ENS1TRS> <ENS1SSTR>SSTARI11</ENS1SSTR> <ENS1TBS>BA68258</ENS1TBS> </RECORD></TABLE></XENTAS>' ;
    
    SELECT ref.value('../@NAME', 'NVARCHAR (5)') AS [NAME] ,
      ref.value('ENS0TN[1]', 'NVARCHAR (30)') AS ENS0TN ,
      ref.value('ENS0SN[1]', 'NVARCHAR (30)') AS ENS0SN ,
      ref.value('ENS0RT[1]', 'NVARCHAR (30)') AS ENS0RT ,
      ref.value('ENS0RG[1]', 'NVARCHAR (30)') AS ENS0RG ,
      ref.value('ENS0AN[1]', 'NVARCHAR (30)') AS ENS0AN ,
      ref.value('ENS0ST[1]', 'NVARCHAR (30)') AS ENS0ST ,
      ref.value('ENS0SS[1]', 'NVARCHAR (30)') AS ENS0SS ,
      ref.value('ENS0TRS[1]', 'NVARCHAR (15)') AS ENS0TRS
    FROM @Xml.nodes('./XENTAS/TABLE/RECORD') ENTEGRATION ( ref )

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

    Tuesday, May 31, 2011 8:22 AM
  • Thank you very much problem solved by your way
    Tuesday, May 31, 2011 8:52 AM