May 4, 2023 at 10:50 am
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
May 4, 2023 at 1:16 pm
<reference_number> is an element, not an attribute.
March 19, 2024 at 12:03 pm
MY_ADV.Pub.value('(reference_number)[1]','varchar(10)')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy