xml import into SQL - SQL Server Forums

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources