Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Basic question

10 views

Skip to first unread message

Charles Sands

unread,

Feb 17, 2011, 4:53:04 PM2/17/11

I'm totally new to working with XML and I'm having trouble with what I
believe to be a simple task. Daily I receive multiple files that
contain xml formatted data. I've included one of the of the entries.
Each file will contain hundreds of similarly formatted entries. My
task is to take the XML file and import it into a SQL table so that we
can use the data. I've managed to BULK INSERT the XML file, and I can
select nodes that are directly off the first level, but I can't go any
deeper. Here is the data I receive:

<mergentRecentEvents extractTime="02/10/2011 16:30:02"
beginTime="02/10/2011 01:00:00 PM" endTime="02/10/2011 04:30:00 PM">
<event eventAddDate="02/10/2011 13:13:25">
<eventGroupType>dividendsEvent</eventGroupType>
<titleCode>DIVP</titleCode>
<title>Dividend Payment</title>
<officialName>Globe Telecom Inc (Philippines)</officialName>
<issueDetail>
<countryCode>PHL</countryCode>
<issueDescription>Ord</issueDescription>
<issueType>Ordinary</issueType>
<primaryTicker>GTMEF</primaryTicker>
<primaryExchange>NBB</primaryExchange>
<identifiers>
<identifier Type="ISIN" Number="PHY272571498" />
</identifiers>
<cins>Y27257149</cins>
<status>A</status>
<lifeStage>Trading</lifeStage>
<iad>74</iad>
<dpfc>S</dpfc>
</issueDetail>
<termsAndConditions>
<companyInfo>
<issueDescription>Ord</issueDescription>
<ticker>GTMEF</ticker>
<exchangeCode>NBB</exchangeCode>
<cusip>Y27257149</cusip>
<paymentKey>0004146020</paymentKey>
<amount>31</amount>
<currencyCode>PHP</currencyCode>
<recordDate>2/22/2011</recordDate>
<payTypeList>
<payType>0</payType>
</payTypeList>
</companyInfo>
<footnoteList>
<footnote Code="406">
<footnoteLiteral>Not subject to PH withholding tax. </
footnoteLiteral>
</footnote>
<footnote Code="216">
<footnoteLiteral>Interim div. for fiscal year beginning
2011. </footnoteLiteral>
</footnote>
</footnoteList>
</termsAndConditions>
</event>
</mergentRecentEvents>

So anything that is just under the event tag, I can retrieve, but
anything that is deeper than that I get a null. For example, if I want
to get countryCode I can't pull it out.

<issueDetail>
<countryCode>PHL</countryCode>
<issueDescription>Ord</issueDescription>
<issueType>Ordinary</issueType>
<primaryTicker>GTMEF</primaryTicker>
<primaryExchange>NBB</primaryExchange>
<identifiers>
<identifier Type="ISIN" Number="PHY272571498" />
</identifiers>
<cins>Y27257149</cins>
<status>A</status>
<lifeStage>Trading</lifeStage>
<iad>74</iad>
<dpfc>S</dpfc>
</issueDetail>

Here is my sample code. Any help is appreciated.
This works:
SELECT tab.col.value('./eventGroupType[1]','varchar(50)') AS
'EventGroupType'
,tab.col.value('./titleCode[1]','varchar(50)') AS 'TitleCode'
,tab.col.value('./title[1]','varchar(50)') AS 'Title'
,tab.col.value('./officialName[1]','varchar(50)') AS 'OfficalName'
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('//event') AS tab(col)
GO

This does not:
SELECT tab.col.value('./eventGroupType[1]','varchar(50)') AS
'EventGroupType'
,tab.col.value('./titleCode[1]','varchar(50)') AS 'TitleCode'
,tab.col.value('./title[1]','varchar(50)') AS 'Title'
,tab.col.value('./officialName[1]','varchar(50)') AS 'OfficalName'
,tab.col.value('./issueDetail/countryCode[1]','varchar(50)') AS
'CountryCode'
FROM [XmlImportTest]
CROSS APPLY
xml_data.nodes('//event') AS tab(col)
GO

pi

unread,

Apr 18, 2011, 9:17:50 AM4/18/11

Declare @x XML = '

select @x.value('./issueDetail/countryCode[1]','varchar(50)');

--Will fail : not a singleton

select @x.value('(./issueDetail/countryCode)[1]','varchar(50)');

--Wil work (just added parentesis)

Hope this helps

Patrick

0 new messages