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-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>

If you need help with an XML query, post a sample XML document and desired results. — David Browne - Microsoft, Commented Oct 23, 2017 at 13:56

Ryan · Accepted Answer · 2017-10-23 20:06:04Z

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).

Stack Exchange Network

Extracting specific XML data from SQL Server column

1 Answer 1

Your Answer

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

Hot Network Questions

Extracting specific XML data from SQL Server column

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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