Importing XML data into a Table

• JSEYMOUR50@YMAIL.COM

  SSC Enthusiast

  Points: 179

  More actions

  May 4, 2023 at 10:50 am

  #4184093

  Hi,

  I am trying to write a script which will import fields from an XML file into a table. Below is the example XML

  <Publication>
  <Addressee SubscriberReference='PCI1' CompanyName='Test company' CompanyNumber=''>
  </Addressee>
  <Content>
  <CommonExtract SystemDate='2023-01-31' SystemProcessRef=''>
  <message xmlns:xsi="" xmlns:xsd="" xmlns="">
  <m_content xmlns="">
  <payee_firm xmlns="">
  <company_name>Test company</company_name>
  <reference>
  <issuing_authority_name>other</issuing_authority_name>
  <type>Other</type>
  <reference_number>111111</reference_number>
  </reference>
  </payee_firm>
  </m_content>
  </message>
  </CommonExtract>
  </Content>
  </Publication>

  Below is the code i am using

  INSERT INTO XMLCommTest (CompanyName, SystemDate, reference_number)
  SELECT
     MY_XML.Pub.value('@CompanyName','varchar(250)')
     ,MY_XML2.Pub.value('@SystemDate','date')
     ,MY_ADV.Pub.value('@reference_number','varchar(10)')
  FROM (SELECT CAST(MY_XML AS xml)
        FROM OPENROWSET(BULK 'C:\cfs\XMLCommTest\LBA0_Comm_Stmts_305741_202301310000.txt', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
        CROSS APPLY MY_XML.nodes('Publication/Addressee') AS MY_XML (Pub) 
    CROSS APPLY MY_XML.nodes('Publication/Content/CommonExtract') AS MY_XML2 (Pub)
    CROSS APPLY MY_XML.nodes('Publication/Content/CommonExtract/message/m_content/payee_firm/reference') AS MY_ADV (Pub)
    ;
  

  the above works fine when i run it to just import company name and system date but when i try to import reference_number it doesn't create any rows in the table and just returns 0 rows affected

  Does any know why it works for company name and system date but not reference number

• ratbak

  Default port

  Points: 1412

  More actions

  May 4, 2023 at 1:16 pm

  #4184171

  <reference_number> is an element, not an attribute.

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  March 19, 2024 at 11:42 am

  #4398115

  wrote:

  It's very easy to do with SmartXML. Check this example <link removed>

  Marking your post as spam. Every single post you make is promoting the same solution!

  ---

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  March 19, 2024 at 12:03 pm

  #4398145

   

  MY_ADV.Pub.value('(reference_number)[1]','varchar(10)')