Extracting specific XML data from SQL Server column

Question

I have XML documents in a SQL Server column. I need to extract a datetime field from this column but the below code isn't working (If I comment out that line everything else works for me) and I was hoping one of you would be able to help please.

The line I am having trouble with is in Bold below.

select x.XPid, t.TA_SEQ, x.PONumber, x.PRNumber,
CONVERT(smalldatetime, x.CreatedDate) as [Date Time XML Received], 
CONVERT(smalldatetime, SWITCHOFFSET(CONVERT(datetimeoffset, x.CreatedDate), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as [Date Time XML Created],
CONVERT(smalldatetime, t.TA_DATE) as [Date Time Task Raised from XML],
CONVERT(smalldatetime, t.TA_EST_DATE) as [Current Target Date],
CONVERT(xml, x.XML_data.Request/OrderRequest/OrderRequestHeader/Extrinsic)[12]/text), 2) as [Original Target Date]
from XMLPost x
join F_PO_HEAD on POH_NUM_ORDER = x.PONumber
join f_tasks t on t.TA_SEQ = POH_FKEY_TA_SEQ
where t.TA_NAME = 'EvoWeb Service'
and XPid = (select min(XPid) from XMLPost xp where x.PONumber = xp.PONumber)
and t.TA_SEQ = 165000
and TA_DATE >={?DateFrom}
and TA_DATE < {?DateTo}
order by [Date Time XML Received]

A sample of the XML data is also below:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.029/cXML.dtd">
<cXML payloadID="1474503828259.295332291.000004918@ofj5NA48HfV5yXDy/619bvB8/m8=" timestamp="2016-09-21T17:23:48-07:00" version="1.2.029" xml:lang="en-US">
<Header>
<From>
<Credential
 domain="NetworkId">
<Identity>AN01015386088</Identity> 
</Credential>
</From>
<To>
<Credential
 domain="NetworkId">
<Identity>an01015485495</Identity> 
</Credential>
<Credential
 domain="internalsupplierid">
<Identity>2034439</Identity> 
</Credential>
<Credential
 domain="buyersystemid">
<Identity>2034439</Identity> 
</Credential>
</To>
<Sender>
<Credential domain="UserId">
<Identity>[email protected]</Identity> 
<SharedSecret>FM</SharedSecret></Credential>
<UserAgent>Buyer 1</UserAgent>
</Sender>
</Header>
<Request deploymentMode="production"><OrderRequest><OrderRequestHeader orderDate="2016-09-21T17:23:46-07:00" orderID="PO12"
orderType="regular" orderVersion="1" type="new">
<Total>
<Money
 alternateAmount="" alternateCurrency=""
 currency="AUD">0.002</Money>
</Total>
<ShipTo>
<Address
 addressID="405945" isoCountryCode="AU">
<Name
 xml:lang="en">405945|1 TIM AVE DUBBO NSW 283</Name>
<PostalAddress
 name="default">
<DeliverTo>405945|1 TIM AVE DUBBO NSW 283</DeliverTo>
<Street>TIM AVE</Street>
<City>DUBBO</City>
<State>NSW</State>
<PostalCode>283</PostalCode>
<Country
 isoCountryCode="AU">Australia</Country>
</PostalAddress>
<Email
 name="default"
 preferredLang="en-AU">[email protected]</Email>
 </Address>
</ShipTo>
<BillTo>
<Address
 addressID="LAHCBT" isoCountryCode="AU">
<Name
 xml:lang="en">LAHCBT|223-239 LIVERPOOL RD ASHFIELD 2131</Name>
<PostalAddress
 name="default">
<Street>LIVERPOOL RD</Street>
<City>ASHFIELD</City>
<State>NSW</State>
<PostalCode>2131</PostalCode>
<Country
 isoCountryCode="AU">Australia</Country>
</PostalAddress>
</Address>
</BillTo>
<Comments>
Comment Type:
General
Comment Body: 
Rephws - Inspect and repair hot water system
Comment By:
J FMANAGEMENT
Comment Date:
2016年09月21日T17:23:38-07:00
</Comments>
<Extrinsic
 name="OriginatingSystemReferenceID">203443924801</Extrinsic>
<Extrinsic
 name="Status">Ordering</Extrinsic>
<Extrinsic
 name="Title">405945 | 1 TIM AVE DUBBO NSW</Extrinsic>
<Extrinsic
 name="Work Program">REP</Extrinsic>
<Extrinsic
 name="Contractor Work Request Reference">24801</Extrinsic>
<Extrinsic
 name="PLC Request Reference Number"/>
<Extrinsic
 name="SPM Assets Reference"/>
<Extrinsic
 name="Current Tracking Status"/>
<Extrinsic
 name="Adjustment Reason"/>
<Extrinsic
 name="Cancellation Reason"/>
<Extrinsic
 name="Requested Date">20 September 2016 09:14 AM</Extrinsic>
<Extrinsic
 name="Target Completion Date">21 September 2016 09:14 AM</Extrinsic>
<Extrinsic
 name="Requester UniqueName">2034439</Extrinsic>
<Extrinsic
 name="Preparer UniqueName">LAUSER</Extrinsic>
<Extrinsic
 name="RequisitionNumber">PR49</Extrinsic>
<Extrinsic
 name="Tracking Status"/>
<Extrinsic name="WOC Date"/>
<Extrinsic
 name="CompanyCode">LA</Extrinsic>
<Extrinsic name="PUName">LA</Extrinsic>
</OrderRequestHeader>
<ItemOut isAdHoc="yes" quantity="1" lineNumber="1">
<ItemID>
<SupplierPartID>185048-REP-NWCA10b-2034439</SupplierPartID>
</ItemID>
<ItemDetail>
<UnitPrice>
<Money alternateCurrency="" alternateAmount="" currency="AUD">0.001</Money>
</UnitPrice>
<Description xml:lang="en">PW</Description>
<UnitOfMeasure>CNT</UnitOfMeasure>
<Classification domain="unspsc">185048</Classification>
<LeadTime>0</LeadTime>
<Extrinsic name="Req. Line No.">1</Extrinsic>
<Extrinsic name="Requester">JFMANAGEMENT</Extrinsic>
<Extrinsic name="PR No.">PR49</Extrinsic>
<Extrinsic name="Work Location">EXT</Extrinsic>
<Extrinsic name="Work Priority">P24</Extrinsic>
<Extrinsic name="Work Classification">PW</Extrinsic>
<Extrinsic name="Inspection DateTime"></Extrinsic>
<Extrinsic name="Inspection Type"></Extrinsic>
<Extrinsic name="Inspector Name"></Extrinsic>
<Extrinsic name="Outcome"></Extrinsic>
</ItemDetail>
<Distribution>
<Accounting name="DistributionCharge">
<Segment description="Percentage" id="100" type="Percentage"></Segment>
<Segment description="ID" id="99999" type="GL Account"></Segment>
<Segment description="ID" id="1000" type="Cost Center"></Segment>
</Accounting>
<Charge>
<Money alternateCurrency="" alternateAmount="" currency="AUD">0.00</Money>
</Charge>
</Distribution>
</ItemOut>
<ItemOut isAdHoc="yes" quantity="1" lineNumber="2">
<ItemID>
<SupplierPartID>183179-REP-NWCA10b-2034439</SupplierPartID>
</ItemID>
<ItemDetail>
<UnitPrice>
<Money alternateCurrency="" alternateAmount="" currency="AUD">0.001</Money>
</UnitPrice>
<Description xml:lang="en">PW</Description>
<UnitOfMeasure>CNT</UnitOfMeasure>
<Classification domain="unspsc">183179</Classification>
<LeadTime>0</LeadTime>
<Extrinsic name="Req. Line No.">2</Extrinsic>
<Extrinsic name="Requester">J FMANAGEMENT</Extrinsic>
<Extrinsic name="PR No.">PR49</Extrinsic>
<Extrinsic name="Work Location">EXT</Extrinsic>
<Extrinsic name="Work Priority">P24</Extrinsic>
<Extrinsic name="Work Classification">PW</Extrinsic>
<Extrinsic name="Inspection DateTime"></Extrinsic>
<Extrinsic name="Inspection Type"></Extrinsic>
<Extrinsic name="Inspector Name"></Extrinsic>
<Extrinsic name="Outcome"></Extrinsic>
</ItemDetail>
<Distribution>
<Accounting name="DistributionCharge">
<Segment description="Percentage" id="100" type="Percentage"></Segment>
<Segment description="ID" id="99999" type="GL Account"></Segment>
<Segment description="ID" id="1000" type="Cost Center"></Segment>
</Accounting>
<Charge>
<Money alternateCurrency="" alternateAmount="" currency="AUD">0.00</Money>
</Charge>
</Distribution>
</ItemOut>
</OrderRequest>
</Request>
</cXML>

• David Browne - Microsoft: If you need help with an XML query, post a sample XML document and desired results.
• McNets: Please add some xml data sample.
• Dave Nixon: added as requested :)

Answer 1

The Target Completion Date should be something like:

x.XML_data.value('(/cXML/Request/OrderRequest/OrderRequestHeader/Extrinsic[@name="Target Completion Date"])[1]', 'datetime') as [Original Target Date]

You may have issues with the xml encoding (UTF-8) and the DTD. I did a quick test and had to remove both the encoding and DTD to select the XML value.

The format of the date may be a challenge. You may have to return it as text and use CONVERT.

I don't know if you have to deal with different time zone offsets, but that can also be problematic since there is no time zone offset in the XML field although I see a tzo in OrderRequestHeader/@oderDate (but you can't assume it is the same tzo).