Importing XML data into a Table

Question

Post reply

Importing XML data into a Table

JSEYMOUR50@YMAIL.COM

SSC Enthusiast

Points: 179

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

March 18, 2024 at 12:39 pm

This was removed by the editor as SPAM
March 18, 2024 at 12:46 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1333 More actions · Answer 1

<reference_number> is an element, not an attribute.

Phil Parkin SSC Guru Points: 246711 More actions · Answer 2

Suliman 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!

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

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