question

murthy avatar image
murthy asked

EXEC() inside OPENXML

Is it possible to use EXEC() inside OPENXML() ?

I have an XML for each order number. I want to output few parameters from XML by using SELECT. I will outputting other fields also which are in the database and not XML.

SO my output should have fields from database and from XML

Regards Murthy

sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I don't understand why you would want to use EXEC() to do that. Assume you have XML in a string variable, like this (very simple example): DECLARE @xmldoc varchar(1000) SET @xmldoc=' ' You'll need a variable to store the document number, and then you can prepare the XML for use with OPENXML: DECLARE @iDoc int EXEC sp_xml_prepare_document @iDoc OUTPUT, @xmldoc To get CustomerID, OrderID, ProductCode, Quantity and OrderLineNo you might do: SELECT * FROM OPENXML (@iDoc, '/ROOT/Order/OrderLine',1) WITH( OrderID int '../@OrderID', CustNo int '../@CustNo', ProductCode varchar(24) '@ProductCode', Quantity int '@Quantity', OrderLineNo int '@OrderNo') You can then use that as a table that you join with your other tables. Let's say for example that you have a table **Customer**: CREATE TABLE Customer (CustNo int primary key, CustName varchar(100)) INSERT INTO Customer (CustNo, CustName) SELECT 555000,'Magnus Ahlkvist' Then you can change your query to the below if you want to join in the CustName from the Customer table. SELECT OrderId, o.CustNo, c.CustName, ProductCode, Quantity, OrderLineNo FROM OPENXML (@iDoc, '/ROOT/Order/OrderLine',1) WITH( OrderID int '../@OrderID', CustNo int '../@CustNo', ProductCode varchar(24) '@ProductCode', Quantity int '@Quantity', OrderLineNo int '@OrderNo') as o INNER JOIN Customer c ON o.CustNo = c.CustNo
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.