Hello,
I have a question regarding querying directly from a XML file from MSSQL 2008. I'm not sure to put this in the SQL forum or the XML forum but I try this forum. I'm very new to working with xml and just starting trying to grasp how to work with XML from SQL since it is related to my new job I will start in next month.
Now I have one Examplefile_e2b_Invoice_v3p41.xml, it contains this:
Code:
<?xml version="1.0" encoding="UTF-8"?>
-<Interchange xsi:schemaLocation="http://www.e2b.no/XMLSchema/e2b_Invoice_Interchange_v3p41.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.e2b.no/XMLSchema"> -<InterchangeHeader> <Version>1.1</Version> <InterchangeId>1234</InterchangeId> <InterchangeTime>2011-10-01T12:00:00</InterchangeTime> -<Originator> <Address>123456789</Address> <AddressQualifier>OrgNr-Avd</AddressQualifier> </Originator> -<Recipient> <Address>987654321</Address> <AddressQualifier>OrgNr-Avd</AddressQualifier> </Recipient> </InterchangeHeader> -<MessageHeader> <Version>1.1</Version> -<DocumentType> <DocumentCode>380</DocumentCode> <DocumentDescriptiveName>FAKTURA</DocumentDescriptiveName> </DocumentType> <MessageReference>123456</MessageReference> <MessageOwner>e2b</MessageOwner> <MessageType>e2bInvoice</MessageType> <MessageVersion>3.4.1</MessageVersion> <language>NO</language> -<MessageOriginator> <Address>123456789</Address> <AddressQualifier>OrgNr-Avd</AddressQualifier> </MessageOriginator> -<MessageRecipient> <Address>987654321</Address> <AddressQualifier>OrgNr-Avd</AddressQualifier> </MessageRecipient> -<Attachment type="INCLUDED"> <AttachmentNumber>1</AttachmentNumber> <CopyIndicator>NO</CopyIndicator> -<CustomContent> <Base64>1234</Base64> </CustomContent> </Attachment> </MessageHeader> -<Invoice language="NO" MessageType="Invoice" MessageOwner="e2b" MessageVersion="3.4.1"> <MessageNumber>1</MessageNumber> <MessageTimestamp>2005-07-01T09:30:47</MessageTimestamp> <NumberOfLines>1</NumberOfLines> -<InvoiceHeader> <InvoiceType codetext="Invoice">380</InvoiceType> <InvoiceStatus codetext="Original">9</InvoiceStatus> <InvoiceNumber>1</InvoiceNumber> <InvoiceDate>2005-07-01</InvoiceDate> -<Supplier> <PartyId>12345</PartyId> <Name>Leverandør1</Name> -<ContactInformation> <PhoneNumber>22220000</PhoneNumber> <FaxNumber>22222001</FaxNumber> <EmailAddress>post@leverandor1.no</EmailAddress> <WebAddress>www.leverandor1.no</WebAddress> </ContactInformation> -<PostalAddress> <Address1>Postboks 5</Address1> <PostalCode>0101</PostalCode> <PostalDistrict>Oslo</PostalDistrict> <CountryCode>NO</CountryCode> </PostalAddress> -<ContactPerson> <Name>Ole Olsen</Name> -<ContactInformation> <PhoneNumber>90090900</PhoneNumber> <EmailAddress>ole@leverandor1.no</EmailAddress> </ContactInformation> </ContactPerson> <OrgNumber>987654321</OrgNumber> <VatId>MVA987654321NO</VatId> </Supplier> -<Buyer> <PartyId>54321</PartyId> <Name>Kjøper1</Name> -<StreetAddress> <Address1>Lilleveien 1</Address1> <PostalCode>3000</PostalCode> <PostalDistrict>Drammen</PostalDistrict> <CountryCode>NO</CountryCode> </StreetAddress> -<ContactPerson> <Name>Hanne Hansen</Name> -<ContactInformation> <PhoneNumber>40040400</PhoneNumber> <EmailAddress>hanne@Kjøper1.no</EmailAddress> </ContactInformation> </ContactPerson> <OrgNumber>998877665</OrgNumber> <VatId>MVA998877665NO</VatId> </Buyer> -<InvoiceReferences> <BuyersOrderNumber>54321</BuyersOrderNumber> <DeliveryDate>2005-08-01</DeliveryDate> </InvoiceReferences> -<Payment> <DueDate>2005-08-01</DueDate> <Currency>NOK</Currency> <KidNumber>123456789</KidNumber> </Payment> </InvoiceHeader> -<InvoiceDetails> -<BaseItemDetails> <LineItemNum>1</LineItemNum> <SuppliersProductId>55555</SuppliersProductId> <Description>Product1</Description> <UnitPrice>2.5</UnitPrice> <LineItemAmount>10</LineItemAmount> <QuantityInvoiced>5</QuantityInvoiced> <UnitOfMeasure>Stk</UnitOfMeasure> -<VatInfo> <VatPercent>25</VatPercent> <VatBaseAmount>11</VatBaseAmount> <VatAmount>2.75</VatAmount> </VatInfo> </BaseItemDetails> </InvoiceDetails> -<InvoiceDiscountChargesAndTax> -<InvoiceCharges> <Description>Frakt</Description> <Percent>6</Percent> <BaseAmount>11</BaseAmount> <Amount>0.66</Amount> -<VatInfo> <VatPercent>25</VatPercent> <VatBaseAmount>0.66</VatBaseAmount> <VatAmount>0.17</VatAmount> </VatInfo> </InvoiceCharges> </InvoiceDiscountChargesAndTax> -<InvoiceSummary> -<InvoiceTotals> <LineItemTotalsAmount>11</LineItemTotalsAmount> <ChargesTotalsAmount>0.66</ChargesTotalsAmount> <GrossAmount>14.50</GrossAmount> <VatTotalsAmount>2.92</VatTotalsAmount> <NetAmount>11.66</NetAmount> <RoundingAmount>0.12</RoundingAmount> </InvoiceTotals> -<VatTotalsInfo> <VatPercent>25</VatPercent> <VatBaseAmount>11.66</VatBaseAmount> <VatAmount>2.92</VatAmount> </VatTotalsInfo> </InvoiceSummary> </Invoice> </Interchange>
I've been testing out to parse out some of the values using select statements, but all I receive is NULL as result. I'm obviously doing something wrong. Here's my code:
Code:
declare @xmlTable table
(
xmlcol XML
)
insert into @xmlTable select InvoicesXML from (select * from openrowset(bulk N'C:\db\Examplefile_e2b_Invoice_v3p41.xml', SINGLE_BLOB) iv) as Invoices(InvoicesXML)
declare @xmlfil xml
select @xmlfil = xmlcol from @xmlTable
select xmlcol.value('(/ROOT/InterchangeHeader/InterchangeId)[1]','VARCHAR(30)') AS InterchangeId, xmlcol.value('(/ROOT/InterchangeHeader/InterchangeTime)[1]','VARCHAR(30)') AS InterchangeTime, xmlcol.value('(/ROOT/InterchangeHeader/Recipient/Address)[1]','VARCHAR(30)') AS Address FROM @xmlTable
I believe the problem is the xml path specified inside xmlcol.value, but I'm not sure. Instead of /ROOT/ I've also tried to use /Interchange/ without succeeding. The sql code is taken and customized from some examples I found using google. Hope anyone can tell me what I'm doing wrong, and whether or not this is the correct form of querying the xml data.
Also, If anyone also could explain what part of the xml the "[1]" in "
select xmlcol.value('(/ROOT/InterchangeHeader/InterchangeId)[1]','VARCHAR(30)')" is referring to, I'd be very happy.
Thanks in advance!
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
