April 16, 2009 at 10:29 am
I have some XML that looks like
{Report}
{table1}
{table1_Group1_Collection}
{table1_Group1 MerchantNumber="123" ...other stuff I don't need...}
{Detail_Collection}
{Detail TerminalID="1" GrossSalesSubmitted_1="200.00" Fees="25.00" /}
{Detail TerminalID="2" GrossSalesSubmitted_1="100.00" Fees="5.00" /}
{Detail TerminalID="Recurring Fees" GrossSalesSubmitted_1="-------" Fees="25.00" /}
{/Detail_Collection}
{/table1_Group1}
{table1_Group1 MerchantNumber="234" ...other stuff I don't need...}
{Detail_Collection}
{Detail TerminalID="1" GrossSalesSubmitted_1="3000.00" Fees="200.00" /}
{Detail TerminalID="Recurring Fees" GrossSalesSubmitted_1="-------" Fees="25.00" /}
{/Detail_Collection}
{/table1_Group1}
{/table1_Group1_Collection}
{/table1}
{/Report}
I need to get the Merchant number out of the table1_Group1 node, and the terminal ID, sales and fees from the details. Different merchants will have different numbers of Terminals.
I'm trying this
SELECT tab.col.value('@MerchantNumber','varchar(50)') AS 'MerchantNumber',
tab.col.value('Detail_Collection/Detail/@TerminalID[1]','varchar(4)') AS 'TerminalID',
tab.col.value('Detail_Collection/Detail/@GrossSalesSubmitted_1[1]','varchar(50)') AS 'GrossSalesSubmitted_1',
tab.col.value('Detail_Collection/Detail/@Fees[1]','varchar(100)') AS 'Fees'
FROM CreditCardXML
CROSS APPLY
xml_data.nodes('//Report/table1/table1_Group1_Collection/table1_Group1') AS tab(col)
GO
But I am getting an error that 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
April 16, 2009 at 1:09 pm
Hi, you need to reference a value to the inner most hierarchy level and go up from there to reference elements from higher hierarchies.
Example (note: I copied your xml structure into a variable @xml for testing purposes):
SELECT tab.col.value('../../@MerchantNumber','varchar(50)') AS 'MerchantNumber',
tab.col.value('@TerminalID[1]','varchar(4)') AS 'TerminalID',
tab.col.value('@GrossSalesSubmitted_1[1]','varchar(50)') AS 'GrossSalesSubmitted_1',
tab.col.value('@Fees[1]','varchar(100)') AS 'Fees'
FROM
@xml.nodes('/Report/table1/table1_Group1_Collection/table1_Group1/Detail_Collection/Detail') AS tab(col)
--
--
--
/*
Resultset:
MerchantNumberTerminalIDGrossSalesSubmitted_1Fees
1231200.0025.00
1232100.005.00
123Recu-------25.00
23413000.00200.00
234Recu-------25.00
*/
April 16, 2009 at 1:18 pm
That seems to have done it.
Thanks.
April 20, 2009 at 8:47 am
Different issue with the same data.
In the previous example, I had cleaned up the {report} tag in an effort to troubleshoot the previous issue.
The actual tag has additional info in it
{Report p1:schemaLocation="WebAddressHere" Name="Chain Merchant Fee Summary by Terminal" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="OtherInfo"}
My query now returns no data, and I believe that it's because the nodes statement uses a path of /Report/ but the actual report tag isn't matching because of the additional information in it.
April 20, 2009 at 11:39 am
Hi,
you need to declare the namespace you're using (xmlns:p1).
The SELECT statement is the same. I Just added the NAMESPACE declaration.
;WITH XMLNAMESPACES (
DEFAULT 'OtherInfo',
'http://www.w3.org/2001/XMLSchema-instance' AS p1
)
SELECT tab.col.value('../../@MerchantNumber','varchar(50)') AS 'MerchantNumber',
tab.col.value('@TerminalID[1]','varchar(4)') AS 'TerminalID',
tab.col.value('@GrossSalesSubmitted_1[1]','varchar(50)') AS 'GrossSalesSubmitted_1',
tab.col.value('@Fees[1]','varchar(100)') AS 'Fees'
FROM
@xml.nodes('/Report/table1/table1_Group1_Collection/table1_Group1/Detail_Collection/Detail') AS tab(col)
Result as above.
April 20, 2009 at 11:46 am
Thank you very much.
April 20, 2009 at 12:11 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply