SQL Server 2016 extract info from XML

Question

I've been through various posts on the same subject but I can't seem to be able to get to the data elements in my XML file.

Here is a snippet of my XML :

    Approved   2021-03-10T14:20:55+13:00   
        NZL2021/MEABC/26913T
        ED1.6      true   false   2021-03-10   
        MEABC
        FLIGHT1      
        
          NZTRG
        
        OAKLAND, United States
        1
        MDH2
         Ever Given
        V1234      
        
          Unofficial Information
          Vessel ETD - 19/03/21
TARE WEIGHT - 2880 KGS
              
        
          1
          true
          AO
          BONELESS BEEF RUMP CAP
          Bovine
          
            US
          
          consumption
          296.4
          271.6
          
            
              Product Statement
              Item No. 81625
Label Approval 2659305 & 91060858
            
          
          
            
              Temperature
              chilled
            
            
              New Zealand Harmonised System Code
              020130
            
            
              Halal Product
              1
            
          
          
            
              CGMU3099999
              
                NZMPIXXXXX
              
            
          
          
            
              29
              CT
              1
              
                MABC
26913
              
            
          
          
            
              SLT
              2021-03-01
              2021-03-01
              false
              
                MEABC
              
            
            
              PRO
              2021-03-02
              2021-03-02
              false
              
                MEABC
              
            
            
              CST
              2021-03-02
              2021-03-10
              false
              
                MEABC

This is what I have tried so far - Figured if I can access one element, I can slowly work on the rest

  if OBJECT_ID('tempdb..#XmlImportTest') is not null
    drop table #XmlImportTest
      
    CREATE TABLE #XmlImportTest(
    xmlFileName VARCHAR(300) NOT NULL,
    xml_data XML NOT NULL
    );
    
    DECLARE @xmlFileName VARCHAR(200) ='K:\Upload\CSNXML\WaybillXml.xml'
    EXEC('INSERT INTO #XmlImportTest(xmlFileName, xml_data)
    
    SELECT ''' + @xmlFileName + ''', xmlData
    FROM(
    SELECT *
    FROM OPENROWSET (BULK ''' + @xmlFileName + ''', SINGLE_BLOB) AS XMLDATA
    ) AS FileImport (XMLDATA)
    ')
    
    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
    
    SELECT @xml = (SELECT xml_data from #XmlImportTest)
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
        
        DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
        
        SELECT @xml = (SELECT xml_data from #XmlImportTest)
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
    ;WITH XMLNAMESPACES ('http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' AS ed)
    
    SELECT 
    
    p.value(N'@ProductItem',N'nvarchar(10)') AS ProductItem
          
    FROM 
    @xml.nodes('/Certificate')
      
    AS A(p)
    CROSS APPLY a.p.nodes(N'Products/Product') AS B(m);

I don't get any results returned.

I get the same result using OPENROWSET as well.

Can someone please tell me how I can access this data element.

AlwaysLearning · Accepted Answer

You seem to be getting confused about XML Namespaces. The example document defines two namespace URIs:

http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd, which has no prefix so is considered to be the "default" namespace of the document.
http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd, which uses the ed namespace prefix that, by eyeballing it, seems to be used on every element in the document so might as well be the default namespace.

Your simplest example is trying to extract the value of the /Certificate/Products/Product/ProductItem elements which could be done as simply as:

with xmlnamespaces (
  default 'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd'
)
select productItem.value(N'text()[1]', N'int') as ProductItem
from @xml.nodes('/Certificate/Products/Product/ProductItem') as p(productItem);

Expanding on this to select a few more values, you can see the @ being used here to access the unitCode attribute of an element:

with xmlnamespaces (
  default 'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd'
)
select
  product.value(N'(ProductItem/text())[1]', N'int') as ProductItem,
  product.value(N'(Exhausted/text())[1]', N'bit') as Exhausted,
  product.value(N'(Origin/text())[1]', N'nvarchar(2)') as Origin,
  product.value(N'(GrossWeight/text())[1]', N'decimal(19,1)') as GrossWeight,
  product.value(N'(GrossWeight/@unitCode)[1]', N'nvarchar(3)') as GrossWeightUnitCode
from @xml.nodes('/Certificate/Products/Product') as p(product);

It should be clear from the above two queries that the namespace prefixes used XPath query don't have to be the same as the ones used in the XML document - it's the namespace URIs themselves that matter. The prefixes in the document are used to link the elements (and sometimes attributes) to their namespace URIs, the prefixes used in XPath can be completely different so long as they reference the correct namespace URIs. e.g. this query returns the same result as the second example above, despite their being no submission prefixes in the source XML:

with xmlnamespaces (
  'http://sancrt.mpi.govt.nz/ecert/2013/ed-multiple-submission-schema.xsd' as multiple,
  'http://sancrt.mpi.govt.nz/ecert/2013/ed-submission-schema.xsd' as submission
)
select
  product.value(N'(submission:ProductItem/text())[1]', N'int') as ProductItem,
  product.value(N'(submission:Exhausted/text())[1]', N'bit') as Exhausted,
  product.value(N'(submission:Origin/text())[1]', N'nvarchar(2)') as Origin,
  product.value(N'(submission:GrossWeight/text())[1]', N'decimal(19,1)') as GrossWeight,
  product.value(N'(submission:GrossWeight/@unitCode)[1]', N'nvarchar(3)') as GrossWeightUnitCode,
product.value(N'(submission:Remarks/submission:Remark/submission:RemarkType/text())[1]', N'nvarchar(50)') as item_remark
from @xml.nodes('/submission:Certificate/submission:Products/submission:Product') as p(product);

SQL Server 2016 extract info from XML

Answers (1)

Related Questions