Sparky1010
asked on
Extract data within XML field in SQL table
Hi there. I am trying to extract some data from an XML column within a table. In the result, I want some of the extracted data from the XML to display as individual fields in a view. I got this to work with another XML request, but this one has a different parameter that is throwing me off.
Here is my SQL query:
When I run this query, it does not error out, but simply does not display any data. If I remove the references to the CROSS APPLY, it displays the data, but obviously not the three fields I want to extract from the XML (i.e. CustID, Status, NewStatus).
Here is what a sample of the data looks like within the XML field:
I have found that if I remove the reference to the bold portion of this code: <CustomerStatusUpdate_Data xmlns="http://tempuri.org/CustomerStatusUpdate_Data.xsd"> that I can pull the data just fine. So I think I just need to accomodate that xmlns="http://tempuri.org/CustomerStatusUpdate_Data.xsd somewhere in my NAMESPACE. This is not a program that I control, so I cannot update the way it writes to the XML field, Can anyone help with this query?
Here is my SQL query:
WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr)
SELECT r.RequestUser, rt.Description, r.Status, r.Succeeded,
r.RequestWorkstation, r.RequestDateTime, r.RequestUID,
b.value('CustID[1]','varchar(6)') AS CustID,
b.value('Status[1]','varchar(10)') AS ProposedStatus,
b.value('NewStatus[1]','varchar(10)') AS OriginalStatus
FROM dbo.Request AS r
INNER JOIN dbo.RequestType AS rt (NOLOCK) on r.RequestTypeUID = rt.RequestTypeUID
CROSS APPLY RequestXML.nodes('/TowerRequest/RequestData/DataSet/diffgr:diffgram/CustomerStatusUpdate_Data/CustomerStatusUpdate') a(b)
Where r.RequestTypeUID = 'a45d85c9-d77b-4710-8b91-0aea987084cc'
When I run this query, it does not error out, but simply does not display any data. If I remove the references to the CROSS APPLY, it displays the data, but obviously not the three fields I want to extract from the XML (i.e. CustID, Status, NewStatus).
Here is what a sample of the data looks like within the XML field:
<TowerRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RequestUID>a8dbc4d0-2cb6-49ce-9fff-0182e16ee1d6</RequestUID>
<RequestTypeUID>a45d85c9-d77b-4710-8b91-0aea987084cc</RequestTypeUID>
<RequestDateTime>2015-03-06T15:11:08.2264505-08:00</RequestDateTime>
<RequestData>
<DataSet>
<xs:schema xmlns:mstns="http://tempuri.org/CustomerStatusUpdate_Data.xsd" xmlns="http://tempuri.org/CustomerStatusUpdate_Data.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" id="CustomerStatusUpdate_Data" targetNamespace="http://tempuri.org/CustomerStatusUpdate_Data.xsd" attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="CustomerStatusUpdate_Data" msdata:IsDataSet="true" msdata:Locale="en-US">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="CustomerStatusUpdate">
<xs:complexType>
<xs:sequence>
<xs:element name="CustID" type="xs:string" minOccurs="0" />
<xs:element name="CustomerUID" msdata:DataType="System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xs:string" minOccurs="0" />
<xs:element name="CustomerName" type="xs:string" minOccurs="0" />
<xs:element name="BillName" type="xs:string" minOccurs="0" />
<xs:element name="BillContact" type="xs:string" minOccurs="0" />
<xs:element name="BillEmail" type="xs:string" minOccurs="0" />
<xs:element name="BillPhone" type="xs:string" minOccurs="0" />
<xs:element name="BillFax" type="xs:string" minOccurs="0" />
<xs:element name="BillMobile" type="xs:string" minOccurs="0" />
<xs:element name="BillingCycle" type="xs:string" minOccurs="0" />
<xs:element name="Terms" type="xs:string" minOccurs="0" />
<xs:element name="CODRequired" type="xs:short" default="0" minOccurs="0" />
<xs:element name="Status" type="xs:short" default="0" minOccurs="0" />
<xs:element name="NewStatus" type="xs:short" default="0" minOccurs="0" />
<xs:element name="EffectiveDate" type="xs:dateTime" minOccurs="0" />
<xs:element name="StatusChangeReason" type="xs:string" minOccurs="0" />
<xs:element name="Balance" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="AgeBal" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="Select" type="xs:short" default="1" minOccurs="0" />
<xs:element name="PrimarySiteID" type="xs:string" minOccurs="0" />
<xs:element name="TemplateName" type="xs:string" minOccurs="0" />
<xs:element name="ToAddress" type="xs:string" minOccurs="0" />
<xs:element name="AgeBal0" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="AgeBal1" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="AgeBal2" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="AgeBal3" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="AgeBal4" type="xs:decimal" default="0" minOccurs="0" />
<xs:element name="ActiveServiceCount" type="xs:short" default="0" minOccurs="0" />
<xs:element name="AgingDays1" type="xs:short" default="0" minOccurs="0" />
<xs:element name="AgingDays2" type="xs:short" default="0" minOccurs="0" />
<xs:element name="AgingDays3" type="xs:short" default="0" minOccurs="0" />
<xs:element name="BillAddressLine1" type="xs:string" minOccurs="0" />
<xs:element name="BillCity" type="xs:string" minOccurs="0" />
<xs:element name="BillState" type="xs:string" minOccurs="0" />
<xs:element name="BillPostalCode" type="xs:string" minOccurs="0" />
<xs:element name="BillFrequency" type="xs:string" minOccurs="0" />
<xs:element name="ExternalCollectionsAgency" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<CustomerStatusUpdate_Data xmlns="http://tempuri.org/CustomerStatusUpdate_Data.xsd">
<CustomerStatusUpdate diffgr:id="CustomerStatusUpdate1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
<CustID>012968</CustID>
<CustomerUID>a546614c-9351-4865-a680-72097688a30b</CustomerUID>
<CODRequired>0</CODRequired>
<Status>0</Status>
<NewStatus>1</NewStatus>
<EffectiveDate>2015-03-06T00:00:00-08:00</EffectiveDate>
<Balance>0</Balance>
<AgeBal>0</AgeBal>
<Select>1</Select>
<AgeBal0>0</AgeBal0>
<AgeBal1>0</AgeBal1>
<AgeBal2>0</AgeBal2>
<AgeBal3>0</AgeBal3>
<AgeBal4>0</AgeBal4>
<ActiveServiceCount>0</ActiveServiceCount>
<AgingDays1>0</AgingDays1>
<AgingDays2>0</AgingDays2>
<AgingDays3>0</AgingDays3>
</CustomerStatusUpdate>
</CustomerStatusUpdate_Data>
</diffgr:diffgram>
</DataSet>
</RequestData>
<TimeZoneOffset>0</TimeZoneOffset>
<Reason />
</TowerRequest>
I have found that if I remove the reference to the bold portion of this code: <CustomerStatusUpdate_Data
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.