• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

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:

 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'

Open in new window


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>

Open in new window


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?
0
Sparky1010
Asked:
Sparky1010
1 Solution
 
ste5anSenior DeveloperCommented:
Yup, there is an inner default namespace declared, which have you not in your T-SQL. E.g. this works:

DECLARE @Data XML = N'
<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>
';

WITH XMLNAMESPACES(
	'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr, 
	' http://tempuri.org/CustomerStatusUpdate_Data.xsd' AS ns
	)
SELECT	b.query('.')
FROM	@Data.nodes('/TowerRequest/RequestData/DataSet/diffgr:diffgram/ns:CustomerStatusUpdate_Data/ns:CustomerStatusUpdate') a(b);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now