The scenario:
You want to import a xml file into SQL Server, but have one xml-file for developing and testing that is easy to read, and one for production that is using really short node names to minimize the size of the file.
The development file could look something like this:
<Root>
<Booking>
<BookingId>1</BookingId>
<BookingName>My own booking</BookingName>
<BookingRow>
<BookingRowId>1</BookingRowId>
</BookingRow>
</Booking>
</Root>
And the production file could look something like this:
<r>
<bo>
<boid>1</boid>
<bn>My own booking</bn>
<br>
<br>1</br>
</br>
</bo>
</r>
And you want to be able to use the same SQL-script to import the file, no matter wether is the production or development file. Try this then:
Declare @xmlfile xml
Declare @docHandle int
Declare @xmlPath varchar(50)
-- Step 1 - Set the base path based on something, filename, or have a startnode in the xml-file
if @fileName = 'C:\prodXmlFile.xml'
@xmlPath = '/r/bo'
else
@xmlPath = '/Root/Booking'
SELECT
@xmlfile=InvoicesXML FROM (
SELECT * FROM OPENROWSET(BULK
@FileName,
SINGLE_BLOB) AS Invoices )
AS Invoices(InvoicesXML)
EXEC sp_xml_preparedocument @docHandle OUTPUT,
@xmlfile
--begin try
SELECT * INTO Bookings
FROM OPENXML(@docHandle,
@xmlPath,2)
WITH (
bookingId int 'bookingId | boid',
bookingName int 'bookingName | bn'
)
-- This is the trick, an xpath expression the takes bookingId if it there, and boid if it is there
-- end try begin catch end catch
EXEC sp_xml_removedocument @docHandle
