0

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>sysadmin@a.com</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">L@j.com.au</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-21T17: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>
  • 1
    If you need help with an XML query, post a sample XML document and desired results. – David Browne - Microsoft Oct 23 '17 at 13:56
  • Please add some xml data sample. – McNets Oct 23 '17 at 13:56
  • added as requested :) – Dave Nixon Oct 23 '17 at 15:02
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.