XmlType with multiple values in resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XmlType with multiple values in resultset

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-19 : 02:51:46

I have a table with xmltype, and some columns
What i wants a result set where rows should be repeated on the bases of number of nodes in one of xml element and it should come as the columns.

SELECT [ConsigneeApptID]
,PartnerApptQueryResponseType.value('declare namespace s="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"; (/s:PartnerApptQueryResponse/s:QueryResults/s:ApptBlock/s:ContentDetailInfoUpdate/s:ContentUSPSSummary/s:SchedulerContentID)','varchar(max)') as location
FROM [PartnerAppointmentQueryResponse]
where PartnerApptQueryResponseType is not null
And TrackingID is null
And ContentCount > 0
And ConsigneeApptID = '107792680'

But I am getting error
Msg 2389, Level 16, State 1, Line 3
XQuery [PartnerAppointmentQueryResponse.PartnerApptQueryResponseType.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I know i can correct it by puttin [1] at the end of my xpath but I wanted to have multiple record on bases of that result
Msg 2389, Level 16, State 1, Line 3
XQuery [PartnerAppointmentQueryResponse.PartnerApptQueryResponseType.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

My xml is follwoing

<mailxml:PartnerApptQueryResponse xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml" mailxml:ConsigneeApptID="107792680" mailxml:SchedulerID="20148" mailxml:SchedulerCorpID="201486961000">
<mailxml:QueryResults>
<mailxml:ApptBlock>
<mailxml:ContentDetailInfoUpdate>
<mailxml:ContentUSPSSummary mailxml:SchedulerContentID="123111402506" mailxml:ConsigneeContentID="100039206">
<mailxml:DestinationDiscountIndicator>No</mailxml:DestinationDiscountIndicator>
<mailxml:ZoneSkippingIndicator>Yes</mailxml:ZoneSkippingIndicator>
<mailxml:Pallets mailxml:Count="1" mailxml:HandlingUnitType="Parcels" mailxml:PalletType="Pallet">
<mailxml:USPSContainerInfo>
<mailxml:ActualPieceCount>314</mailxml:ActualPieceCount>
<mailxml:ContainerWeight>16.7</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>492155789123456000085231</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>
</mailxml:Pallets>
<mailxml:Perishable>No</mailxml:Perishable>
<mailxml:MailClass>4</mailxml:MailClass>
<mailxml:RateType>L</mailxml:RateType>
<mailxml:ProcessingCategory>MP</mailxml:ProcessingCategory>
<mailxml:PriorityMailIndicator>No</mailxml:PriorityMailIndicator>
<mailxml:VerificationLocation>DMU</mailxml:VerificationLocation>
<mailxml:NewsIndicator>No</mailxml:NewsIndicator>
<mailxml:EvsIndicator>No</mailxml:EvsIndicator>
<mailxml:MailContentName>Akhbar e Jahan</mailxml:MailContentName>
<mailxml:MailPreparerID>20191</mailxml:MailPreparerID>
</mailxml:ContentUSPSSummary>
</mailxml:ContentDetailInfoUpdate>
<mailxml:ContentDetailInfoUpdate>
<mailxml:ContentUSPSSummary mailxml:SchedulerContentID="123111385003" mailxml:ConsigneeContentID="100039189">
<mailxml:DestinationDiscountIndicator>No</mailxml:DestinationDiscountIndicator>
<mailxml:ZoneSkippingIndicator>Yes</mailxml:ZoneSkippingIndicator>
<mailxml:Pallets mailxml:Count="2" mailxml:HandlingUnitType="Parcels" mailxml:PalletType="Pallet">
<mailxml:USPSContainerInfo>
<mailxml:ActualPieceCount>924</mailxml:ActualPieceCount>
<mailxml:ContainerWeight>49.2</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>552005758123456000085601</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>172005748123456000085591</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>
</mailxml:Pallets>
<mailxml:Perishable>No</mailxml:Perishable>
<mailxml:MailClass>4</mailxml:MailClass>
<mailxml:RateType>L</mailxml:RateType>
<mailxml:ProcessingCategory>MP</mailxml:ProcessingCategory>
<mailxml:PriorityMailIndicator>No</mailxml:PriorityMailIndicator>
<mailxml:VerificationLocation>DMU</mailxml:VerificationLocation>
<mailxml:NewsIndicator>No</mailxml:NewsIndicator>
<mailxml:EvsIndicator>No</mailxml:EvsIndicator>
<mailxml:MailContentName>Akhbar e Jahan</mailxml:MailContentName>
<mailxml:MailPreparerID>20191</mailxml:MailPreparerID>
</mailxml:ContentUSPSSummary>
</mailxml:ContentDetailInfoUpdate>
</mailxml:ApptBlock>
</mailxml:QueryResults>
</mailxml:PartnerApptQueryResponse>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 11:36:55

The value function is soooo picky - it is not good enough if you have only one value that will be returned, it requires that there is no possibility that no more than one value will bereturned even if your data changes. So you could try the following alternative.

I tested the query syntax using the variable, but did not install and test against your table. If it complains, please post the error message you get? Ditto if you are not getting the data you are looking for.


SELECT
   [ConsigneeApptID],
   c.query
   (
       'declare namespace s="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"; 
      data(@s:SchedulerContentID)'
   ).value('.', 'varchar(max)') AS location
FROM
   [PartnerAppointmentQueryResponse]
   CROSS APPLY PartnerApptQueryResponseType.nodes
   (
      'declare namespace s="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"; 
      /s:PartnerApptQueryResponse/s:QueryResults/s:ApptBlock/s:ContentDetailInfoUpdate/s:ContentUSPSSummary'
   ) T(c)
WHERE
   PartnerApptQueryResponseType IS NOT NULL
   AND TrackingID IS NULL
   AND ContentCount > 0
   AND ConsigneeApptID = '107792680'

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-20 : 00:56:26

Thanks Sunit

One more thing i wanted is that all this should be put into another result set like

Select Y.*
From
(
--WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
T.[ConsigneeApptID] as ApptID,
x.i.value('(./mailxml:UniqueContainerBarcode)[1]','varchar(max)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets/mailxml:USPSContainerInfo') AS x(i)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
) Y

where Y.Barcode is not NULL

and .........

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-20 : 07:04:16

When you want to use the namespace in a sub-query, you have to put the namespace definition before all else, as in:


WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
Select Y.*
From
(
--WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
T.[ConsigneeApptID] as ApptID,
x.i.value('(./mailxml:UniqueContainerBarcode)[1]','varchar(max)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets/mailxml:USPSContainerInfo') AS x(i)
WHERE
PartnerApptQueryResponseType is not null 
And ContentCount > 0 
) Y

Also, the preceding statement (if any) must be terminated with a semi-colon.

You could also use a CTE instead of a sub-query if that is your preference. I find it more readable because I read from top to bottom

. If you want to use CTE, then again, the namespace clause must preced all else, as in


WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
, cte as
(
--WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
T.[ConsigneeApptID] as ApptID,
x.i.value('(./mailxml:UniqueContainerBarcode)[1]','varchar(max)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets/mailxml:USPSContainerInfo') AS x(i)
WHERE
PartnerApptQueryResponseType is not null 
And ContentCount > 0 
) 
SELECT * FROM CTE

BTW, if you have only one namespace or if most of your elements are in one namespace, you may want to declare that namespace as default. No particular performance advantage, but cuts down on the amount of typing you have to do. They syntax (from http://msdn.microsoft.com/en-us/library/ms177400.aspx) is like this:

WITH XMLNAMESPACES ('uri1' as ns1, 
                    'uri2' as ns2,
                    DEFAULT 'uri2')

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-22 : 02:26:59

One problem i am having is that my taht my Pallets block comes

<mailxml:Pallets mailxml:Count="1" mailxml:HandlingUnitType="Parcels" mailxml:PalletType="Pallet">
<mailxml:PalletPreSortLevel mailxml:Count="1" mailxml:MaildatCode="A">
<mailxml:USPSContainerInfo>
<mailxml:ContainerWeight>41.4</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>553364104221032410468</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>
</mailxml:PalletPreSortLevel>
</mailxml:Pallets>

or as previously mentioned as

<mailxml:Pallets mailxml:Count="2" mailxml:HandlingUnitType="Parcels" mailxml:PalletType="Pallet">
<mailxml:USPSContainerInfo>
<mailxml:ActualPieceCount>924</mailxml:ActualPieceCount>
<mailxml:ContainerWeight>49.2</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>552005758123456000085601</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>172005748123456000085591</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>
</mailxml:Pallets>

Can i have a single xpath to resolve it get get my barcodes?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-22 : 08:22:46

Not quite clear to me what your requirement is, but you can always find all the descendants with a specific name regardless of the parent lineage using the descendant axis.
So, if you were trying to find all UniqueContainerBarCodes under Pallets, regardless of how the UniqueContainerBarCode element is nested under the Pallets element, you can do this:


WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
   c.query( 'data(.)')
FROM
   YourTable
   CROSS APPLY XMLCol.nodes ('/mailxml:Pallets/descendant::mailxml:UniqueContainerBarcode') T(c)

I have not quite tested it (it parses); if it does not work, please post back the error message?

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-22 : 08:44:06

Thanks a lot sunita
I am using
WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)

SELECT
T.[ConsigneeApptID] as ApptID,
T.QueryRequestID as WithBarcodeQueryRequestID,
x.i.value('(./mailxml:UniqueContainerBarcode)[1]','varchar(max)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets/descendant::mailxml:USPSContainerInfo') AS x(i)
WHERE
PartnerApptQueryResponseType is not null
and T.ConsigneeApptID = '107926727'

only problem i am having is that it is just getting only first barcode 562017804221229400416 rather then all

sample data is

<mailxml:USPSContainerInfo>
<mailxml:ContainerWeight>665.0</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>562017804221229400416</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>710205404221229400791</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>361818604221229400322</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>462806804221229400510</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>550785504221229400494</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>152827504221229400541</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>026435804221229400447</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>521337304221229400401</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>163708704221229400604</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>815120304221229400338</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>347462704221229400697</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>646040404221229400729</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>810437404221229400619</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>115856304221229400854</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>707676204221229400291</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>208457004221229400588</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>232760004221229400572</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>504074304221229400760</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>823520704221229400354</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>736351704221229400713</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>654816504221229400838</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>026754704221229400635</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>651851104221229400822</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>573487804221229400666</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>443576404221229400776</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>478086604221229400479</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>067540404221229400744</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>201340704221229400682</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>587372604221229400526</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>687777504221229400432</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>138244604221229400385</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>010537104221229400651</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>362382504221229400463</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>000224704221229400276</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>837338104221229400807</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>746701404221229400369</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>868753004221229400307</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>361078304221229400557</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-22 : 08:58:26

My whole query looks like
Select distinct HeaderRecord.hdrOriginalFlatFileJobID,
HeaderRecord.hdrJobNameTitleAndIssue,
csmLabelUserInformationLine1,
csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
--HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,
dbo.ufnGetFASTDateFormat (Closeout_Info.TruckArrival) as FASTTruckArrival,
dbo.ufnGetFASTDateFormat (Closeout_Info.UnloadStart) as FASTTruckUnloadStart,
dbo.ufnGetFASTDateFormat (Closeout_Info.UnloadEnd) as FASTTruckUnloadEnd,
Closeout_Info.[Status] as AppointmentCloseoutStatus,
CASE
WHEN (select COUNT(Y.Barcode)) > 0 THEN (select COUNT(Y.Barcode))
WHEN (select COUNT(Y.Barcode)) = 0 THEN NULL
END as ContainerCount
From
PartnerAppointmentQueryResponse paqr Inner Join PartnerProfile ON PartnerProfile.PartnerID = paqr.PartnerID
left outer join
(
SELECT
T.[ConsigneeApptID] as ApptID,
T.QueryRequestID as WithBarcodeQueryRequestID,
x.i.value('(./mailxml:UniqueContainerBarcode)[1]','varchar(max)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets//mailxml:USPSContainerInfo') AS x(i)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
) Y ON Y.ApptID = paqr.ConsigneeApptID And paqr.QueryRequestID = Y.WithBarcodeQueryRequestID
LEFT OUTER Join ContainerSummaryRecord on Y.Barcode = ContainerSummaryRecord.csmLabelIMContainerOrIMTrayBarcode
LEFT OUTER Join HeaderRecord on HeaderRecord.fkJobID = ContainerSummaryRecord.fkJobID
LEFT OUTER JOIN Closeout_Info ON paqr.ConsigneeApptID = Closeout_Info.ConsigneeID
group by HeaderRecord.hdrOriginalFlatFileJobID,HeaderRecord.hdrJobNameTitleAndIssue, csmLabelUserInformationLine1, csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
-- HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,Closeout_Info.TruckArrival,Closeout_Info.UnloadStart,Closeout_Info.UnloadEnd,Closeout_Info.[Status]

I can't use c.query as you mentioned as I have to use the result in a match with outer join data

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-22 : 09:02:40

I don't have the table dbo.PartnerAppointmentQueryResponse that you are doing the CROSS APPLY against.
I suspect it may be something in the join condition.
CROSS APPLY is just like an inner join (well, almost), so see if the join is causing the data to drop out.

I copied your XML data and ran the following test.
That seems to work fine using the descendant axis. See if this will help you figure it out.
If not, please post the table DDL and some data so I can run the same query you are running and get the same results?

DECLARE @xml XML;

SET @xml = '<mailxml:USPSContainerInfo xmlns:mailxml="http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml"  >
<mailxml:ContainerWeight>665.0</mailxml:ContainerWeight>
<mailxml:UniqueContainerBarcode>562017804221229400416</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>710205404221229400791</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>361818604221229400322</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>462806804221229400510</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>550785504221229400494</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>152827504221229400541</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>026435804221229400447</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>521337304221229400401</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>163708704221229400604</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>815120304221229400338</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>347462704221229400697</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>646040404221229400729</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>810437404221229400619</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>115856304221229400854</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>707676204221229400291</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>208457004221229400588</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>232760004221229400572</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>504074304221229400760</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>823520704221229400354</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>736351704221229400713</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>654816504221229400838</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>026754704221229400635</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>651851104221229400822</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>573487804221229400666</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>443576404221229400776</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>478086604221229400479</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>067540404221229400744</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>201340704221229400682</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>587372604221229400526</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>687777504221229400432</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>138244604221229400385</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>010537104221229400651</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>362382504221229400463</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>000224704221229400276</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>837338104221229400807</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>746701404221229400369</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>868753004221229400307</mailxml:UniqueContainerBarcode>
<mailxml:UniqueContainerBarcode>361078304221229400557</mailxml:UniqueContainerBarcode>
</mailxml:USPSContainerInfo>';


WITH XMLNAMESPACES
(
      'http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' 
      AS mailxml
),
XMLTable AS (
                SELECT
                   @xml AS col
            )
SELECT
   i.query('data(.)')
FROM
   XMLTable T
   CROSS APPLY
col.nodes(
    '/mailxml:USPSContainerInfo/descendant::mailxml:UniqueContainerBarcode'
) AS x(i)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-23 : 10:11:38

Thanks a million Sunita
My Final working Query is

WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)

Select distinct HeaderRecord.hdrOriginalFlatFileJobID,
HeaderRecord.hdrJobNameTitleAndIssue,
csmLabelUserInformationLine1,
csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
--HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,
dbo.ufnGetFASTDateFormat (Closeout_Info.TruckArrival) as FASTTruckArrival,
dbo.ufnGetFASTDateFormat (Closeout_Info.UnloadStart) as FASTTruckUnloadStart,
dbo.ufnGetFASTDateFormat (Closeout_Info.UnloadEnd) as FASTTruckUnloadEnd,
Closeout_Info.[Status] as AppointmentCloseoutStatus,
CASE
WHEN (select COUNT( Cast(Y.Barcode as varchar(max)))) > 0 THEN (select COUNT( Cast(Y.Barcode as varchar(max))))
WHEN (select COUNT( Cast(Y.Barcode as varchar(max)))) = 0 THEN NULL
END as ContainerCount
From
PartnerAppointmentQueryResponse paqr Inner Join PartnerProfile ON PartnerProfile.PartnerID = paqr.PartnerID
left outer join
(
SELECT
T.[ConsigneeApptID] as ApptID,
T.QueryRequestID as WithBarcodeQueryRequestID,
x.i.query('data(.)') as Barcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets//mailxml:USPSContainerInfo/mailxml:UniqueContainerBarcode') AS x(i)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
) Y ON Y.ApptID = paqr.ConsigneeApptID And paqr.QueryRequestID = Y.WithBarcodeQueryRequestID
LEFT OUTER Join ContainerSummaryRecord on Cast(Y.Barcode as varchar(max)) = ContainerSummaryRecord.csmLabelIMContainerOrIMTrayBarcode and ContainerSummaryRecord.csmLabelIMContainerOrIMTrayBarcode is not null And Cast(Y.Barcode as varchar(max)) is not null
LEFT OUTER Join HeaderRecord on HeaderRecord.fkJobID = ContainerSummaryRecord.fkJobID and HeaderRecord.hdrHistoryStatus = 'C'
LEFT OUTER JOIN Closeout_Info ON paqr.ConsigneeApptID = Closeout_Info.ConsigneeID and paqr.ConsigneeApptID is not null
group by HeaderRecord.hdrOriginalFlatFileJobID,HeaderRecord.hdrJobNameTitleAndIssue, csmLabelUserInformationLine1, csmLabelUserInformationLine2,
paqr.ConsigneeApptID,
-- HeaderRecord.fkJobID,
PartnerProfile.PartnerID,
PartnerProfile.PartnerName,
paqr.QueryRequestID,
paqr.AppointmentQueryStatus,
paqr.PartnerSchedulerCorpID,
paqr.PartnerSchedulerID,
paqr.PartnerSchedulerCRID,
paqr.LastQueriedAt,
paqr.TrackingID,
paqr.FacilityName,
paqr.FacilityNumber,
paqr.TrailerLength,
paqr.ScheduledAppt,
paqr.ContentCount,
paqr.EffectiveStartDate,
paqr.EffectiveEndDate,
paqr.ApptTime,
paqr.DayFrequency,
paqr.WeekFrequency,
paqr.RecurringAppointmentStatus,
paqr.QueryType,Closeout_Info.TruckArrival,Closeout_Info.UnloadStart,Closeout_Info.UnloadEnd,Closeout_Info.[Status]

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-23 : 12:19:55

You are welcome, glad it worked out!

So you didn't really need to use the descendant axis. I thought in one of your earlier posts, the nesting of the elements was not the same from one element to the next, which resulted in you being unable to find some of the :UniqueContainerBarcode elements. But, if you got it working as you want it, its only of academic interest, I guess.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-23 : 14:25:16

Thanks Sunita,
Now I am working on one of another stored procedure Query Where
I have to get data for two of the nodes[one is nested in another] of the same table response xml
It is under ContentUSPSSummary block i.e

ConsigneeContentID UniqueContainerBarcode
A 23123123123
A 213123123123
A 213123123
B NULL
C SDFDSFDSFsdf
D sdfsdf32432
D 3123

Currently my query is
WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)

SELECT
T.QueryRequestID as WithBarcodeQueryRequestID,
x.i.query('data(.)') as Barcode,
y.j.query('data(.)') as ContentID
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/mailxml:Pallets//mailxml:USPSContainerInfo/mailxml:UniqueContainerBarcode') AS x(i)
Cross Apply
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary/@mailxml:ConsigneeContentID') AS y(j)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
And T.ConsigneeApptID = '107926728'

But it is repeating barcodes even they are not in that particular ConsigneeContentID block

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-23 : 15:17:21

I can see that that is what it will do, because we are not correlating the two cross-apply conditions. Can you post a sample XML string which I can use to test? Hard for me to visualize the element tree without some data to look at.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-23 : 17:29:28

I have posted a detailed post at microsoft forums for tsql with sample xml. You can check it at [url]http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d140536b-ea3f-421e-8185-1c63369e3694[/url]

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-23 : 19:10:02

The person who responded there has given you the same answer that I was describing in my earlier post,which is that, your second cross apply must correlate with the first cross apply. See below. There are two things to note here:

1. In the first cross apply, you are doing the cross apply against table T AND, you go down only to the ContentUPSSummary node.

2. In the second cross apply, you are doing the cross apply against table Y from step 1 (which causes this cross apply to correlate with the previous one).

Once you have done that, it is a matter of using XPATH expression to get the data you want.

WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
	T.QueryRequestID as WithBarcodeQueryRequestID,
	x.i.query('data(.)') as Barcode,
	y.j.query('data(./@mailxml:ConsigneeContentID)') as ContentID
FROM
	dbo.PartnerAppointmentQueryResponse AS T
Cross Apply 
	T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary') AS y(j)
CROSS APPLY
	Y.PartnerApptQueryResponseType.nodes('/mailxml:Pallets//mailxml:USPSContainerInfo/mailxml:UniqueContainerBarcode') AS x(i)
WHERE
	PartnerApptQueryResponseType IS NOT NULL
	AND ContentCount > 0
	AND T.ConsigneeApptID = '107926728'

As before, I have not tested the code because I don't have the dbo.PartnerAppointmentQueryResponse table.

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-04-24 : 06:03:24

Thaks Sunita

WITH XMLNAMESPACES('http://idealliance.org/maildat/Specs/md091/mailxml80B/mailxml' AS mailxml)
SELECT
T.QueryRequestID as WithBarcodeQueryRequestID,
T1.x.value('@mailxml:ConsigneeContentID[1]', 'varchar(64)') AS ConsigneeContentID,
T2.x.value('(text())[1]', 'varchar(128)') AS UniqueContainerBarcode
FROM
dbo.PartnerAppointmentQueryResponse AS T
CROSS APPLY
T.PartnerApptQueryResponseType.nodes('/mailxml:PartnerApptQueryResponse/mailxml:QueryResults/mailxml:ApptBlock/mailxml:ContentDetailInfoUpdate/mailxml:ContentUSPSSummary') AS T1(x)
CROSS APPLY
T1.x.nodes('mailxml:Pallets//mailxml:USPSContainerInfo/mailxml:UniqueContainerBarcode') AS T2(x)
WHERE
PartnerApptQueryResponseType is not null
And ContentCount > 0
And T.ConsigneeApptID = '107926728'

did the trick

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-24 : 07:57:27

Welcome. Glad you finally got there

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources