February 23, 2017 at 8:08 am
Please, please, please could someone help me. I am new to XML and am really struggling with reading this file from a client as no rows returned. Can anyone tell me what I am doing wrong (ultimately want to read all columns but keeping simple for now)?
Declare @xml XML
Select @xml=BulkColumn from OpenRowSet(Bulk'C:\Modelware\ThirdPartyData\QuadLabs\XChangeFinanceWS2.xml',Single_blob) x;
;WITH XMLNAMESPACES
(
'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr,
'urn:schemas-microsoft-com:xml-msdata' AS msdata,
DEFAULT 'http://services.myservice.com'
)
SELECT
c.value('@diffgr:id[1]','varchar(10)') AS Payments,
c.value('Transaction_ID[1]','varchar(10)') AS Transaction_ID
FROM @xml.nodes('DataSet/diffgr:diffgram/MapperData/Payments') T(c)
I have attached the full XML but also included first part below;
<DataSet>
<xs:schema xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="MapperData">
<xs:element name="MapperData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Payments">
<xs:complexType>
<xs:sequence>
<xs:element name="Transaction_ID" type="xs:long" minOccurs="0"/>
<xs:element name="Company_ID" type="xs:string" minOccurs="0"/>
<xs:element name="Branch_ID" type="xs:short" minOccurs="0"/>
<xs:element name="Branch_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Agent_ID" type="xs:string" minOccurs="0"/>
<xs:element name="Agent_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Booking_Ref" type="xs:string" minOccurs="0"/>
<xs:element name="Transaction_Of" type="xs:string" minOccurs="0"/>
<xs:element name="Transaction_Type" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Date" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Mode" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Amount" type="xs:string" minOccurs="0"/>
<xs:element name="Receipt_No" type="xs:string" minOccurs="0"/>
<xs:element name="Card_Code" type="xs:string" minOccurs="0"/>
<xs:element name="Card_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Credit_Card_No" type="xs:string" minOccurs="0"/>
<xs:element name="Credit_Card_Expiry" type="xs:string" minOccurs="0"/>
<xs:element name="Credit_Card_Charges" type="xs:decimal" minOccurs="0"/>
<xs:element name="Bank_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Reference_No" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Remarks" type="xs:string" minOccurs="0"/>
<xs:element name="Supplier_Code" type="xs:string" minOccurs="0"/>
<xs:element name="Supplier_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Client_Code" type="xs:string" minOccurs="0"/>
<xs:element name="Client_Name" type="xs:string" minOccurs="0"/>
<xs:element name="Record_Imported" type="xs:unsignedByte" minOccurs="0"/>
<xs:element name="Company_Currency" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Currency" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Currency_Amount" type="xs:decimal" minOccurs="0"/>
<xs:element name="Additional_Remarks" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Type" type="xs:string" minOccurs="0"/>
<xs:element name="No_Of_Installments" type="xs:short" minOccurs="0"/>
<xs:element name="PG_Txn_Number" type="xs:string" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="PaymentSegments">
<xs:complexType>
<xs:sequence>
<xs:element name="Payment_Segment_ID" type="xs:long" minOccurs="0"/>
<xs:element name="Transaction_ID" type="xs:long" minOccurs="0"/>
<xs:element name="Booking_Ref" type="xs:string" minOccurs="0"/>
<xs:element name="Product_Booking_ID" type="xs:string" minOccurs="0"/>
<xs:element name="Product_Type" type="xs:string" minOccurs="0"/>
<xs:element name="Amount" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Currency" type="xs:string" minOccurs="0"/>
<xs:element name="Payment_Currency_ROE" type="xs:decimal" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<MapperData>
<Payments diffgr:id="Payments1" msdata:rowOrder="0">
<Transaction_ID>384</Transaction_ID>
<Company_ID>RC</Company_ID>
<Branch_ID>1</Branch_ID>
etc...
February 23, 2017 at 8:42 am
What you have posted is the inline schema definition, not the XML data content apart from the last 5 lines, can you please post fully consumable content?
😎
To check the correct name space definition try use a wildcard namespace SELECT
T.c.value('@diffgr:id[1]','varchar(10)') AS Payments,
T.c.value('*:Transaction_ID[1]','varchar(10)') AS Transaction_ID
FROM @xml.nodes('*:DataSet/*:diffgram/*:MapperData/*:Payments') T(c)
February 23, 2017 at 12:45 pm
Hey, thanks so much for looking at this! I previously thought it might be best to just post enough of the XML Data to see the beginning of the relevant content but have now included the full content below (reduced the number of entries to make it more manageable). I initially also attached the entire original file but see no way of accessing this attachment in my original post above - so not sure what happens to attached files.
<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="MapperData" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="MapperData" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Payments">
<xs:complexType>
<xs:sequence>
<xs:element name="Transaction_ID" type="xs:long" minOccurs="0" />
<xs:element name="Company_ID" type="xs:string" minOccurs="0" />
<xs:element name="Branch_ID" type="xs:short" minOccurs="0" />
<xs:element name="Branch_Name" type="xs:string" minOccurs="0" />
<xs:element name="Agent_ID" type="xs:string" minOccurs="0" />
<xs:element name="Agent_Name" type="xs:string" minOccurs="0" />
<xs:element name="Booking_Ref" type="xs:string" minOccurs="0" />
<xs:element name="Transaction_Of" type="xs:string" minOccurs="0" />
<xs:element name="Transaction_Type" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Date" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Mode" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Amount" type="xs:string" minOccurs="0" />
<xs:element name="Receipt_No" type="xs:string" minOccurs="0" />
<xs:element name="Card_Code" type="xs:string" minOccurs="0" />
<xs:element name="Card_Name" type="xs:string" minOccurs="0" />
<xs:element name="Credit_Card_No" type="xs:string" minOccurs="0" />
<xs:element name="Credit_Card_Expiry" type="xs:string" minOccurs="0" />
<xs:element name="Credit_Card_Charges" type="xs:decimal" minOccurs="0" />
<xs:element name="Bank_Name" type="xs:string" minOccurs="0" />
<xs:element name="Reference_No" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Remarks" type="xs:string" minOccurs="0" />
<xs:element name="Supplier_Code" type="xs:string" minOccurs="0" />
<xs:element name="Supplier_Name" type="xs:string" minOccurs="0" />
<xs:element name="Client_Code" type="xs:string" minOccurs="0" />
<xs:element name="Client_Name" type="xs:string" minOccurs="0" />
<xs:element name="Record_Imported" type="xs:unsignedByte" minOccurs="0" />
<xs:element name="Company_Currency" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Currency" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Currency_Amount" type="xs:decimal" minOccurs="0" />
<xs:element name="Additional_Remarks" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Type" type="xs:string" minOccurs="0" />
<xs:element name="No_Of_Installments" type="xs:short" minOccurs="0" />
<xs:element name="PG_Txn_Number" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="PaymentSegments">
<xs:complexType>
<xs:sequence>
<xs:element name="Payment_Segment_ID" type="xs:long" minOccurs="0" />
<xs:element name="Transaction_ID" type="xs:long" minOccurs="0" />
<xs:element name="Booking_Ref" type="xs:string" minOccurs="0" />
<xs:element name="Product_Booking_ID" type="xs:string" minOccurs="0" />
<xs:element name="Product_Type" type="xs:string" minOccurs="0" />
<xs:element name="Amount" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Currency" type="xs:string" minOccurs="0" />
<xs:element name="Payment_Currency_ROE" type="xs:decimal" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<MapperData>
<Payments diffgr:id="Payments1" msdata:rowOrder="0">
<Transaction_ID>384</Transaction_ID>
<Company_ID>RC</Company_ID>
<Branch_ID>1</Branch_ID>
<Branch_Name>Rennies Travel</Branch_Name>
<Agent_ID>3</Agent_ID>
<Agent_Name>Website</Agent_Name>
<Booking_Ref>831</Booking_Ref>
<Transaction_Of>Client</Transaction_Of>
<Transaction_Type>Payment</Transaction_Type>
<Payment_Date>15 Feb 2017</Payment_Date>
<Payment_Mode>Credit</Payment_Mode>
<Payment_Amount>1894.33</Payment_Amount>
<Receipt_No>443</Receipt_No>
<Card_Code>DC</Card_Code>
<Card_Name>Diners</Card_Name>
<Credit_Card_No>3613XXXXXXXX04</Credit_Card_No>
<Credit_Card_Expiry>12/2018</Credit_Card_Expiry>
<Credit_Card_Charges>38.6700</Credit_Card_Charges>
<Bank_Name>TraderootMOTO</Bank_Name>
<Reference_No>443</Reference_No>
<Payment_Remarks />
<Supplier_Code />
<Supplier_Name />
<Client_Code>405</Client_Code>
<Client_Name>Mrs. PETUNIA ramoitheki</Client_Name>
<Record_Imported>0</Record_Imported>
<Company_Currency>ZAR</Company_Currency>
<Payment_Currency>ZAR</Payment_Currency>
<Payment_Currency_Amount>1894.330000</Payment_Currency_Amount>
<Additional_Remarks>ServiceType: AIR || ServiceName: HLA-CPT || ClientMobile: 086-1777479 || ClientEmail: petunia.ramoitheki@renniestravel.com || TravelStartDate: 22 Feb 2017 || Destination: Cape Town || TotalPax: 1 || PassengerBooked: LEANA Hanscomb</Additional_Remarks>
<Payment_Type />
<No_Of_Installments>1</No_Of_Installments>
<PG_Txn_Number />
</Payments>
<Payments diffgr:id="Payments2" msdata:rowOrder="1">
<Transaction_ID>386</Transaction_ID>
<Company_ID>RC</Company_ID>
<Branch_ID>1</Branch_ID>
<Branch_Name>Rennies Travel</Branch_Name>
<Agent_ID>3</Agent_ID>
<Agent_Name>Website</Agent_Name>
<Booking_Ref>835</Booking_Ref>
<Transaction_Of>Client</Transaction_Of>
<Transaction_Type>Payment</Transaction_Type>
<Payment_Date>15 Feb 2017</Payment_Date>
<Payment_Mode>Credit</Payment_Mode>
<Payment_Amount>10991.46</Payment_Amount>
<Receipt_No>445</Receipt_No>
<Card_Code>AX</Card_Code>
<Card_Name>American Express</Card_Name>
<Credit_Card_No>3770XXXXXXXX532</Credit_Card_No>
<Credit_Card_Expiry>6/2018</Credit_Card_Expiry>
<Credit_Card_Charges>268.0800</Credit_Card_Charges>
<Bank_Name>TraderootMOTO</Bank_Name>
<Reference_No>445</Reference_No>
<Payment_Remarks />
<Supplier_Code />
<Supplier_Name />
<Client_Code>786</Client_Code>
<Client_Name>Mrs Marsha Eckhardt</Client_Name>
<Record_Imported>0</Record_Imported>
<Company_Currency>ZAR</Company_Currency>
<Payment_Currency>ZAR</Payment_Currency>
<Payment_Currency_Amount>10991.460000</Payment_Currency_Amount>
<Additional_Remarks>ServiceType: AIR || ServiceName: CPT-GBE || ClientMobile: 021-8612400 || ClientEmail: marsha.eckhardt@renniestravel.com || TravelStartDate: 20 Feb 2017 || Destination: Gaborone || TotalPax: 2 || PassengerBooked: CARLA LAMPRECHT~ Khanyisa Songca</Additional_Remarks>
<Payment_Type />
<No_Of_Installments>1</No_Of_Installments>
<PG_Txn_Number />
</Payments>
<PaymentSegments diffgr:id="PaymentSegments1" msdata:rowOrder="0">
<Payment_Segment_ID>821</Payment_Segment_ID>
<Transaction_ID>384</Transaction_ID>
<Booking_Ref>831</Booking_Ref>
<Product_Booking_ID>1781</Product_Booking_ID>
<Product_Type>CHG</Product_Type>
<Amount>38.67</Amount>
<Payment_Currency>ZAR</Payment_Currency>
<Payment_Currency_ROE>1.000000</Payment_Currency_ROE>
</PaymentSegments>
<PaymentSegments diffgr:id="PaymentSegments2" msdata:rowOrder="1">
<Payment_Segment_ID>822</Payment_Segment_ID>
<Transaction_ID>384</Transaction_ID>
<Booking_Ref>831</Booking_Ref>
<Product_Booking_ID>936</Product_Booking_ID>
<Product_Type>AIR</Product_Type>
<Amount>932.03</Amount>
<Payment_Currency>ZAR</Payment_Currency>
<Payment_Currency_ROE>1.000000</Payment_Currency_ROE>
</PaymentSegments>
<PaymentSegments diffgr:id="PaymentSegments150" msdata:rowOrder="149">
<Payment_Segment_ID>970</Payment_Segment_ID>
<Transaction_ID>453</Transaction_ID>
<Booking_Ref>924</Booking_Ref>
<Product_Booking_ID>1048</Product_Booking_ID>
<Product_Type>AIR</Product_Type>
<Amount>2499.00</Amount>
<Payment_Currency>ZAR</Payment_Currency>
<Payment_Currency_ROE>1.000000</Payment_Currency_ROE>
</PaymentSegments>
</MapperData>
</diffgr:diffgram>
</DataSet>
February 23, 2017 at 7:34 pm
It looks like the namespaces are still wreaking havoc, especially since the schema content defines a few then the element defines a few more.I also had my dev copy complain about using UTF-16 as in the XML encoding.
That said I can get content back with a few edits, mostly adding in some wildcard namespaces:
;WITH XMLNAMESPACES
(
'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr,
'urn:schemas-microsoft-com:xml-msdata' AS msdata,
DEFAULT 'http://services.myservice.com'
)
SELECT
c.value('@*:id[1]','varchar(10)') AS Payments,
c.value('*:Transaction_ID[1]','varchar(10)') AS Transaction_ID
FROM @xml.nodes('*:DataSet/*:diffgram/*:MapperData/*:Payments') T(c)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2017 at 10:56 pm
Matt! And Eirikur! You are my heroes!
Eirikur, I see you were suggesting similar but I clearly did not interpret the converted emoticons back correctly.
But Matt, yours enabled me to get the data finally without any struggle! I am ecstatic and so appreciative to you both for taking the time to help. I would never have come right and Matt, your comment about namespaces wreaking havoc is enlightening and provides some sort of explanation why I struggled so much with something so apparently simple. I first struggled with this with a SSIS package until I eventually resorted to an XQuery. From what I see I would never have come right with SSIS.
If anyone has any further clarification on the issue with the file content provided to me I would appreciate it as I would like to get back to the provider of the Web Service where I receive the data from.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply