December 19, 2007 at 6:36 am
Hi,
A quick question (hopefully), the following T-SQL OpenXML statement has been taken from the Microsoft site, I was wondering if anyone knew how to return the ContactName in the select, for example '../@CustomerID' returns the customer id from the Order tag, so how do i return data from the customer tag, '.../@ContactName' does not work, any suggestions.
if you cant see the XML it's here (http://msdn2.microsoft.com/en-us/library/aa276847(SQL.80).aspx)
Thanks in advance
declare @idoc int
declare @doc varchar(1000)
set @doc ='
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
'
--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
December 19, 2007 at 7:31 am
It's just up one more level
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity',
ContactName nvarchar(50) '../../@ContactName')
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2007 at 7:40 am
That works a treat. I was pulling my hair out with that one.
Many thanks,
August 2, 2010 at 3:11 pm
Can someone please help me extracting the below one?
<Return>
<SOURCE PROCESSSTATECODE="30" ERRORCODE="4835" FORMNAME="26" FIELDNAME="">
<DEST MESSAGE_ID="10289">
<MSG> If some investment is not at risk, you will have to return.
</MSG>
<MAPPING>
<MAP MAP_ID="11375" ENTITY="" FORM="" FIELD="" PAGE="w6l0p1257.aspx" DDM_TYPE="Fallback" OVERRIDE_MSG="" />
</MAPPING>
</DEST>
</SOURCE>
</Return>
I tried the following; but I am not able to extract the data which is out of <MAPPING> -
SELECT * into #t FROM OPENXML( @i, '/Return/SOURCE/DEST/MAPPING/MAP',2)
WITH (
Entity VARCHAR(10) '/Return/@ENTITY',
MapId VARCHAR(10) '@MAP_ID',
Form VARCHAR(10) '@FORM',
Field VARCHAR(10) '@FIELD',
Page VARCHAR(1000) '@PAGE',
DDMType VARCHAR(10) '@DDM_TYPE',
OverrideMessage VARCHAR(10) '@OVERRIDE_MSG',
[Message] VARCHAR(MAX) '/Return/SOURCE/DEST/MSG',
MessageId VARCHAR(10) '/Return/SOURCE/DEST/@MESSAGE_ID',
ProcessStateCode VARCHAR(10) '/Return/SOURCE/@PROCESSSTATECODE',
ErrorCode VARCHAR(10) '/Return/SOURCE/@ERRORCODE',
FormName VARCHAR(10) '/Return/SOURCE/@FORMNAME',
FieldName VARCHAR(10) '/Return/SOURCE/@FIELDNAME'
)
I am getting the first record value of the second part (starting from MessageId to right) gets repeated; the first part is fine.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply