Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 xml import into SQL

Author  Topic 

peter.lamb.zw
Starting Member

4 Posts

Posted - 2014-10-08 : 06:18:47
I am getting (0 row(s) affected) when I run the following query using the xml below can any one help.


DECLARE @xml XML
DECLARE @character VARCHAR(MAX)

SELECT @character = x.y
FROM OPENROWSET( BULK 'C:\SuperrepXML\order.xml', SINGLE_CLOB ) x(y)

SELECT
t.c.value('@WrntyID', 'nvarchar(12)') WrntyID,
t.c.value('@Date', 'Date') Date,
s.c.value('@AgentName', 'nvarchar(50)') AgentName,
s.c.value('@AgentExternalID', 'nvarchar(5)') AgentExternalID,
c.c.value('@AccountExternalID', 'nvarchar(50)') AccountExternalID,
c.c.value('@AccountName', 'nvarchar(5)') AccountName


FROM @xml.nodes('/SalesTransaction/TransactionHeader/TransactionHeaderFields') AS t(c)
CROSS APPLY t.c.nodes('/SalesTransaction/TransactionHeader/SalesRepFields') s(c)
CROSS APPLY s.c.nodes('/SalesTransaction/TransactionHeader/AccountFields') c(c)



xml

<?xml version="1.0" encoding="UTF-8"?>

-<SalesTransaction>


-<TransactionHeader>


-<TransactionHeaderFields>

<WrntyID>9823702</WrntyID>

<Type>Sales Order</Type>

<Status>Submited</Status>

<Date>2014-10-08T08:51:05Z</Date>

<DeliveryDate>2014-10-07</DeliveryDate>

<Remark/>

</TransactionHeaderFields>


-<CatalogFields>

<CatalogID>Default Catalog</CatalogID>

<CatalogDescription/>

<CatalogPriceFactor>1</CatalogPriceFactor>

<CatalogExpirationDate>2014-06-23</CatalogExpirationDate>

</CatalogFields>


-<SalesRepFields>

<AgentName>Phillip Chinomona</AgentName>

<AgentExternalID>02</AgentExternalID>

<AgentEmail>philchin70@gmail.com</AgentEmail>

</SalesRepFields>


-<AccountFields>

<AccountWrntyID>7328909</AccountWrntyID>

<AccountExternalID>60102</AccountExternalID>

<AccountName>HAZ MOTORS</AccountName>

</AccountFields>


-<BillingFields>

<BillToName>HAZ MOTORS</BillToName>

<BillToStreet>86 ROBERT MUGABE ROAD KADOMA</BillToStreet>

<BillToCity/>

<BillToState/>

<BillToCountry>Zimbabwe</BillToCountry>

<BillToZipCode/>

<BillToPhone/>

</BillingFields>


-<ShippingFields>

<ShipToExternalID/>

<ShipToName>HAZ MOTORS</ShipToName>

<ShipToStreet>86 ROBERT MUGABE ROAD KADOMA</ShipToStreet>

<ShipToCity/>

<ShipToState/>

<ShipToCountry>Zimbabwe</ShipToCountry>

<ShipToZipCode/>

<ShipToPhone/>

</ShippingFields>


-<Totals>

<Currency>$</Currency>

<SubTotal>92.69</SubTotal>

<SubTotalAfterItemsDiscount>92.69</SubTotalAfterItemsDiscount>

<GrandTotal>92.69</GrandTotal>

<DiscountPercentage>0</DiscountPercentage>

<TaxPercentage>0</TaxPercentage>

</Totals>

<ContactPersonFields/>

<TransactionCustomFields/>

</TransactionHeader>


-<TransactionLines>


-<TransactionLine>


-<TransactionLineFields>

<UnitsQuantity>10</UnitsQuantity>

<UnitPrice>4.03</UnitPrice>

<UnitDiscountPercentage>0</UnitDiscountPercentage>

<UnitPriceAfterDiscount>4.03</UnitPriceAfterDiscount>

<TotalUnitsPriceBeforeDiscount>40.3</TotalUnitsPriceBeforeDiscount>

<TotalUnitsPriceAfterDiscount>40.3</TotalUnitsPriceAfterDiscount>

<DeliveryDate>2014-10-07</DeliveryDate>

<UOMQuantity>0</UOMQuantity>

<TransactionWrntyID>9823702</TransactionWrntyID>

<TransactionExternalID/>

<LineNumber>0</LineNumber>

</TransactionLineFields>


-<ItemFields>

<ItemWrntyID>14802508</ItemWrntyID>

<ItemExternalID>10111</ItemExternalID>

<ItemMainCategory>Crystal</ItemMainCategory>

<ItemMainCategoryCode>10111</ItemMainCategoryCode>

<ItemName>CRYSTAL MINTS HAWKER BALER</ItemName>

</ItemFields>

<TransactionLineCustomFields/>

</TransactionLine>


-<TransactionLine>


-<TransactionLineFields>

<UnitsQuantity>5</UnitsQuantity>

<UnitPrice>4.03</UnitPrice>

<UnitDiscountPercentage>0</UnitDiscountPercentage>

<UnitPriceAfterDiscount>4.03</UnitPriceAfterDiscount>

<TotalUnitsPriceBeforeDiscount>20.15</TotalUnitsPriceBeforeDiscount>

<TotalUnitsPriceAfterDiscount>20.15</TotalUnitsPriceAfterDiscount>

<DeliveryDate>2014-10-07</DeliveryDate>

<UOMQuantity>0</UOMQuantity>

<TransactionWrntyID>9823702</TransactionWrntyID>

<TransactionExternalID/>

<LineNumber>0</LineNumber>

</TransactionLineFields>


-<ItemFields>

<ItemWrntyID>14802509</ItemWrntyID>

<ItemExternalID>10114</ItemExternalID>

<ItemMainCategory>Crystal</ItemMainCategory>

<ItemMainCategoryCode>10114</ItemMainCategoryCode>

<ItemName>CRYSTAL FRUIT DROPS HAWKER BALER</ItemName>

</ItemFields>

<TransactionLineCustomFields/>

</TransactionLine>


-<TransactionLine>


-<TransactionLineFields>

<UnitsQuantity>5</UnitsQuantity>

<UnitPrice>4.03</UnitPrice>

<UnitDiscountPercentage>0</UnitDiscountPercentage>

<UnitPriceAfterDiscount>4.03</UnitPriceAfterDiscount>

<TotalUnitsPriceBeforeDiscount>20.15</TotalUnitsPriceBeforeDiscount>

<TotalUnitsPriceAfterDiscount>20.15</TotalUnitsPriceAfterDiscount>

<DeliveryDate>2014-10-07</DeliveryDate>

<UOMQuantity>0</UOMQuantity>

<TransactionWrntyID>9823702</TransactionWrntyID>

<TransactionExternalID/>

<LineNumber>0</LineNumber>

</TransactionLineFields>


-<ItemFields>

<ItemWrntyID>14802531</ItemWrntyID>

<ItemExternalID>11111</ItemExternalID>

<ItemMainCategory>Crystal</ItemMainCategory>

<ItemMainCategoryCode>11111</ItemMainCategoryCode>

<ItemName>CRYSTAL - CHOC TOFFEE HAWKER BALER</ItemName>

</ItemFields>

<TransactionLineCustomFields/>

</TransactionLine>


-<TransactionLine>


-<TransactionLineFields>

<UnitsQuantity>3</UnitsQuantity>

<UnitPrice>4.03</UnitPrice>

<UnitDiscountPercentage>0</UnitDiscountPercentage>

<UnitPriceAfterDiscount>4.03</UnitPriceAfterDiscount>

<TotalUnitsPriceBeforeDiscount>12.09</TotalUnitsPriceBeforeDiscount>

<TotalUnitsPriceAfterDiscount>12.09</TotalUnitsPriceAfterDiscount>

<DeliveryDate>2014-10-07</DeliveryDate>

<UOMQuantity>0</UOMQuantity>

<TransactionWrntyID>9823702</TransactionWrntyID>

<TransactionExternalID/>

<LineNumber>0</LineNumber>

</TransactionLineFields>


-<ItemFields>

<ItemWrntyID>14802532</ItemWrntyID>

<ItemExternalID>11112</ItemExternalID>

<ItemMainCategory>Crystal</ItemMainCategory>

<ItemMainCategoryCode>11112</ItemMainCategoryCode>

<ItemName>CRYSTAL - TOFFEE HAWKER BALER</ItemName>

</ItemFields>

<TransactionLineCustomFields/>

</TransactionLine>

</TransactionLines>

</SalesTransaction>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-08 : 14:34:06
All your data is in nodes, not attributes, so instead of using @WrntyID etc., use WrntyID[1] etc. When you prefix an @, SQL XML looks for an attribute with that name
SELECT 
t.c.value('WrntyID[1]', 'nvarchar(12)') WrntyID,
t.c.value('Date[1]', 'Date') Date,
s.c.value('AgentName[1]', 'nvarchar(50)') AgentName,
s.c.value('AgentExternalID[1]', 'nvarchar(5)') AgentExternalID,
c.c.value('AccountExternalID[1]', 'nvarchar(50)') AccountExternalID,
c.c.value('AccountName[1]', 'nvarchar(5)') AccountName
FROM @xml.nodes('/SalesTransaction/TransactionHeader/TransactionHeaderFields') AS t(c)
CROSS APPLY t.c.nodes('/SalesTransaction/TransactionHeader/SalesRepFields') s(c)
CROSS APPLY s.c.nodes('/SalesTransaction/TransactionHeader/AccountFields') c(c)
Go to Top of Page
   

- Advertisement -