openxml question - 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 2000 Forums

Transact-SQL (2000)

openxml question

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-27 : 14:31:45

I have read up on the openxml section in books online, but am having a bit of trouble relating it back to my source xml document.

I have a BASDA xml order, as below containing multiple nodes for customer ID, orderID, addresses, orderlines etc.

How do I relate that to the openxml procedure.

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

<Order xsi:schemaLocation='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01 http://www.ebis-xml.net/schemas/order-v3.xsd http://www.ebuild-xml.org.uk/projectReferences-v1 http://www.ebuild-xml.org.uk/schemas/projectReferences-v1.xsd' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
<OrderHead>
<Schema>
<Version>3.02.1</Version>
</Schema>
<Stylesheet>
<StylesheetOwner>redror.co.uk</StylesheetOwner>
<StylesheetName>RedrowOrder</StylesheetName>
<Version>1.01</Version>
<StylesheetType>xsl</StylesheetType>
</Stylesheet>
<Parameters>
<Language>en_GB</Language>
<DecimalSeparator>.</DecimalSeparator>
<Precision>20.3</Precision>
</Parameters>
<OriginatingSoftware>
<SoftwareManufacturer>Redrow</SoftwareManufacturer>
<SoftwareProduct>Order Generator</SoftwareProduct>
<SoftwareVersion>1.01</SoftwareVersion>
</OriginatingSoftware>
<TestFlag />
<OrderType Code='CAO' Codelist='BASDA'>Call Off Order</OrderType>
<OrderCurrency>
<Currency Code='GBP' Codelist='BASDA'>GB Pounds Sterling</Currency>
</OrderCurrency>
<Checksum>1234</Checksum>
</OrderHead>
<OrderReferences>
<CostCentre>02</CostCentre>
<BuyersOrderNumber>1296/0-0162438</BuyersOrderNumber>
<ProjectCode Preserve='true'>1296</ProjectCode>
</OrderReferences>
<HB:ProjectReferences xmlns:HB='http://www.ebuild-xml.org.uk/projectReferences-v1'>
<HB:NameOfProjectOrDevelopment>The Hawthorns</HB:NameOfProjectOrDevelopment>
</HB:ProjectReferences>
<OrderDate>2005-06-06</OrderDate>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>GEO152</BuyersCodeForSupplier>
</SupplierReferences>
<Party>Moores Furniture Group</Party>
<Address>
<AddressLine>Thorp Arch Trading Estate</AddressLine>
<AddressLine>Wetherby</AddressLine>
<AddressLine>W. Yorks</AddressLine>
<AddressLine>LS23 7DD</AddressLine>
</Address>
<Contact>
<Name>Sales Dept</Name>
<Switchboard>tel no</Switchboard>
<Fax>fax no</Fax>
<Email>paul@moores.co.uk</Email>
</Contact>
</Supplier>
<Buyer>
<BuyerReferences>
<SuppliersCodeForBuyer>RED001</SuppliersCodeForBuyer>
</BuyerReferences>
<Party>Redrow Homes (Northern) Ltd</Party>
<Address>
<AddressLine>Redrow House</AddressLine>
<AddressLine>Preston Brook</AddressLine>
<AddressLine>Cheshire</AddressLine>
</Address>
<Contact>
<Name>Alan Dawson</Name>
<Department>tel no</Department>
<Fax>tel no</Fax>
<Email>paul@bedrock.org</Email>
</Contact>
</Buyer>
<Delivery>
<DeliverTo>
<Party>The Hawthorns IV</Party>
<Address>
<AddressLine>St. David's Park</AddressLine>
<AddressLine>Ewloe</AddressLine>
<AddressLine>Flintshire</AddressLine>
</Address>
<Contact>
<Name>Alan Dawson</Name>
<Switchboard>tel no</Switchboard>
<Fax>tel no</Fax>
<Email>paul@redrow.co.uk</Email>
</Contact>
</DeliverTo>
</Delivery>
<InvoiceTo>
<Party>Redrow Homes Northern Ltd</Party>
<Address>
<AddressLine>PO Box 12345</AddressLine>
<AddressLine>RedrowHouse</AddressLine>
<AddressLine>St. David's Park</AddressLine>
<AddressLine>Flintshire</AddressLine>
</Address>
<Contact>
<Name>Central Payments Manager</Name>
<Switchboard>tel no</Switchboard>
</Contact>
</InvoiceTo>
<OrderLine>
<LineNumber>1</LineNumber>
<OrderLineReferences>
<ContractOrderReference>872</ContractOrderReference>
<BuyersOrderLineReference>129600100799</BuyersOrderLineReference>
</OrderLineReferences>
<HB:ProjectReferences xmlns:HB='http://www.ebuild-xml.org.uk/projectReferences-v1'>
<HB:SiteLocation>Site Location</HB:SiteLocation>
<HB:BlockReference>Block ref</HB:BlockReference>
<HB:FloorLevel>Numeric, Ground=0</HB:FloorLevel>
<HB:HouseType>
<HB:BuyersCode Preserve='true'>F4H144.1/1G1</HB:BuyersCode>
<HB:Details>
<HB:Elevation>Tudor Render</HB:Elevation>
<HB:SpecificationLevel>Platinum</HB:SpecificationLevel>
<HB:StandardDescription>Gladstone</HB:StandardDescription>
</HB:Details>
</HB:HouseType>
<HB:CustomerChoice>
<HB:Choose>
<HB:ItemDescription>Range (Cat1)</HB:ItemDescription>
<HB:Choice>Maggiore/Quartz Fleck</HB:Choice>
</HB:Choose>
</HB:CustomerChoice>
<HB:ConstructionStage>8:Plaster</HB:ConstructionStage>
<HB:OrderSets>
<HB:RequiredForThisOrderOrLine>1</HB:RequiredForThisOrderOrLine>
<HB:BuyersNameForOrderSet>KITCHEN UNITS</HB:BuyersNameForOrderSet>
</HB:OrderSets>
<HB:PlotNumber Preserve='true'>289</HB:PlotNumber>
<HB:Handing>As</HB:Handing>
<HB:ProductGroup>KITCHN</HB:ProductGroup>
<HB:WorkBreakdownStructure>Work BreakDown</HB:WorkBreakdownStructure>
</HB:ProjectReferences>
<Quantity UOMCodelist='UNECE' UOMCode='PCE' UOMDescription='Piece'>
<Packsize>1</Packsize>
<Amount>1</Amount>
</Quantity>
<Price UOMCodelist='UNECE' UOMCode='PCE' UOMDescription='Piece'>
<UnitPrice>300.00</UnitPrice>
</Price>
<LineTotal>300.00</LineTotal>
<Delivery>
<PreferredDate>2000-05-25</PreferredDate>
</Delivery>
<OrderLineInformation>Quotation reference number</OrderLineInformation>
<ExtendedDescription>Additional information about what is required</ExtendedDescription>
</OrderLine>

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-27 : 19:40:24

Ok, I just did a small portion as your XML statement is huge.

This below XPATHS to the AddressLine node.

You can have alot of functionality with OPENXML, I would suggest learning the basics of the XPATH syntax. Also you need a valid XML doc, the one you pasted is not, since it doesn't have a root node. I added one called "yak". Also note you have alot of "'" in your string, to set this to a variable in sql and get this example to work you have to replace "'" with "''".

[


DECLARE @xml varchar(8000), @idoc int
SET @xml = 'ALL YOUR XML'
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT *
FROM OPENXML (@idoc, '/yak/Supplier/Address/AddressLine')
		WITH(AddressLine varchar(100) '.')

EXEC sp_xml_removedocument @idoc

Hope this helps you!

Mike Petanovitch

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-28 : 16:00:50

You say my XML doesn't have a root node, so I checked and the closing Tag is <Order>, which corresponds to the Order at the start of the following Tag

<Order xsi:schemaLocation='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01 http://www.ebis-xml.net/schemas/order-v3.xsd http://www.ebuild-xml.org.uk/projectReferences-v1 http://www.ebuild-xml.org.uk/schemas/projectReferences-v1.xsd' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>

This must be the root of the xml doc as the next tag relates to the child of Order <OrderHeader>

Or am I misunderstanding your comment

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-28 : 17:07:01

Ok, then you are just not closing your Order node. Add </Order> to the end of the doc.

It doesn't make the difference because its not the main point. Change the XPATH query to reflect the proper node names. In the above case "yak" to "order".

Anyway, the above is not a complete answer as I don't know the requirements but you can use that above example as a starting point.

Also note: @idoc can be reused for multiple OPENXML selects, ie if you wanted to insert orderdetails in one table and addressdetails in another. You only have to call the sp_xml_preparedocument once and just pass the @idoc (doc reference in memory) around.

Mike Petanovitch

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-29 : 13:32:28

OK that makes sense, but do I have to specify multiple Xpaths
if I want to get the content from all of the tags to insert into a SQL table.

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-29 : 13:55:13

Depends on what you need. You have alot of flexiblity in the WITH clause of the OPENXML statement to map nodes to columns. However it has to make sense relationally. Try to invision your xml doc in one table. If you can't then you probably need to break it up.

Mike Petanovitch

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-29 : 14:49:53

Thanks for the help, looks like I've got a bit of leg work to do

N.B I have also looked at the Bulk Load facility, which allows you to define a mapping document to the nodes in the xml file.
Which would be the best way to go?

mfemenel
Professor Frink

1421 Posts

Posted - 2005-04-29 : 14:57:19

If you're importing data straight into a table then I'd say BCP. If you need to use the data as part of a stored procedure or do any sort of manipulation with it, then openxml. IMHO.

Mike
"oh, that monkey is going to pay"

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-04-29 : 15:15:46

Here is a good link which gives you numerious options in importing XML into the db.
http://www.perfectxml.com/articles/XML/ImportXMLSQL.asp

note, if you want to use XML Bulk Load you have to install SQLXML 3.0

Mike Petanovitch

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources