CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2008
Tabber
Tabber

Reputation: 21

Nested XML Output

I am trying to query out information from nested xml tags. I have been though many web searches and tried everything I have found, but nothing is getting me my desired result without specifying the exact numeric element in the nest that I want.

The code I am trying to run is this:

DECLARE @XmlIN XML
SELECT @xmlin  = '
<ShippingScheduleResponse   xmlns="rrn:org.xcbl:schemas/xcbl/v4_0/materialsmanagement/v1_0/materialsmanagement.xsd">
  <ShippingScheduleResponseHeader>
    <ShippingScheduleHeader>
        <ScheduleReferences>
        <OtherScheduleReferences>
          <ReferenceCoded xmlns="rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd">
            <ReferenceTypeCoded>Other</ReferenceTypeCoded>
            <ReferenceTypeCodedOther>HomeOwnerOccupied</ReferenceTypeCodedOther>
            <ReferenceDescription>N</ReferenceDescription>
          </ReferenceCoded>
          <ReferenceCoded xmlns="rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd">
            <ReferenceTypeCoded>Other</ReferenceTypeCoded>
            <ReferenceTypeCodedOther>WorkOrderNumber</ReferenceTypeCodedOther>
            <ReferenceDescription>7456634</ReferenceDescription>
          </ReferenceCoded>
          <ReferenceCoded xmlns="rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd">
            <ReferenceTypeCoded>Other</ReferenceTypeCoded>
            <ReferenceTypeCodedOther>SSID</ReferenceTypeCodedOther>
            <ReferenceDescription>293283</ReferenceDescription>
          </ReferenceCoded>
        </OtherScheduleReferences>
      </ScheduleReferences>
    </ShippingScheduleHeader>
  </ShippingScheduleResponseHeader>
</ShippingScheduleResponse>
'

SELECT

    RefLineValue = ref.value('@xmlns', 'varchar(300)'),
    RefTypeOther = ref.value('(ReferenceTypeCodedOther)[1]', 'varchar(50)'),
    RefDescription = ref.value('(ReferenceDescription)[1]', 'varchar(50)')
FROM
    @XmlIN.nodes('/ShippingScheduleResponse/ShippingScheduleResponseHeader/ShippingScheduleHeader/ScheduleReferences') AS XTbl(schref)
CROSS APPLY
    schref.nodes('OtherScheduleReferences/ReferenceCoded') AS XTbl2(ref)

I am trying to get the values for the referenceTypeCodedOther and ReferenceDescription into a table.

Best scenario would be to get the Reference Description for the ReferenceTypeCodedOther that is equal to SSID, without having to put in a hardcoded st.value('(//*:ReferenceCoded[7][1]//*:ReferenceDescription)[1]', 'char(200)')

Any help would be greatly appreciated. thanks

Upvotes: 2

Views: 46

Answers (1)

Andrea
Andrea

Reputation: 12355

Your code is almost perfect: to extract values from your xml you just have to handle namespaces using the WITH keyword:

;WITH XMLNAMESPACES (
    'rrn:org.xcbl:schemas/xcbl/v4_0/materialsmanagement/v1_0/materialsmanagement.xsd' as ns1,
    'rrn:org.xcbl:schemas/xcbl/v4_0/core/core.xsd' as ns2)
SELECT
  RefTypeOther = ref.value('(ns2:ReferenceTypeCoded)[1]', 'varchar(50)'),
  RefTypeOther = ref.value('(ns2:ReferenceTypeCodedOther)[1]', 'varchar(50)'),
  RefDescription = ref.value('(ns2:ReferenceDescription)[1]', 'varchar(50)')
FROM
    @XmlIN.nodes('/ns1:ShippingScheduleResponse/ns1:ShippingScheduleResponseHeader/ns1:ShippingScheduleHeader/ns1:ScheduleReferences') AS XTbl(schref)
CROSS APPLY
    schref.nodes('ns1:OtherScheduleReferences/ns2:ReferenceCoded') AS XTbl2(ref)

Results:

enter image description here

Now you can filter by RefDescription = 'SSID'

Upvotes: 1

Related Questions