xQuery Error: query returns multiple nodes even with [1] predicate

mercredi 25 avril 2012 12:06

I'm using the following xQuery to extract email addresses from an XML file:

select 
temp.tp.value('for $i in (com:Addresses/com:Address), $j in ($i/Media/ExternalId) where ($j eq "EMAIL") return ($i/com:Address)[1]', 'nvarchar(100)') email

				
from 
@tp.nodes('ThirdPartyReport/ThirdParty') as temp(tp);

<com:ThirdParty xmlns:com="http://www.bsb.com/extraction/common">
  <com:ExternalId>x</com:ExternalId>
  <com:Name>x</com:Name>
  <com:Addresses>
    <com:Address>
      <com:Sending>true</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>POST</com:ExternalId>
      </com:Media>
      <com:PostCode>x</com:PostCode>
      <com:Town>x</com:Town>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>FAX</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>PHONE</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>WEB</com:ExternalId>
      </com:Media>
    </com:Address>
  </com:Addresses>
</com:ThirdParty>

I thought that the [1] predicate in the return part of the FLWOR expression would guarantee a singleton but it doesn't. What's wrong with the query?

Toutes les réponses

mercredi 25 avril 2012 13:10

Auteur de réponse

It's not clear what your expected results look like. You could wrap the whole statement in brackets and add the [1], but I don't think that would return what you want.

How about something like this instead?

DECLARE @tp XML 

SET @tp = '<com:ThirdParty xmlns:com="http://www.bsb.com/extraction/common">
  <com:ExternalId>x</com:ExternalId>
  <com:Name>x</com:Name>
  <com:Addresses>
    <com:Address>
      <com:Sending>true</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>POST</com:ExternalId>
      </com:Media>
      <com:PostCode>x</com:PostCode>
      <com:Town>x</com:Town>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>FAX</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>PHONE</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>WEB</com:ExternalId>
      </com:Media>
    </com:Address>
  </com:Addresses>
</com:ThirdParty>'

-- Return Address items with an ExternalId=EMAIL child
;WITH XMLNAMESPACES ( 'http://www.bsb.com/extraction/common' AS com )
SELECT 
	temp.tp.value('(com:Sending/text())[1]', 'varchar(10)') sending,
	temp.tp.value('(com:Address/text())[1]', 'varchar(max)') email
FROM @tp.nodes('com:ThirdParty/com:Addresses/com:Address[com:Media/com:ExternalId[.="EMAIL"]]') as temp(tp);

mercredi 25 avril 2012 13:42

I need a list with all the com:Thirdparty/com:ExternalId, Name, First name and corresponding email address. However some clients have more than one email address, while i only require one. I tried to make a query which selects the first email address but it still returned both of them.

What's wrong with using a FLWOR expression? In my opinion they're much easier to understand than the xpath expression you seem to prefer.

This is the entire query:

select 
temp.tp.value('(com:ExternalId)[1]', 'int') id,
temp.tp.value('(com:Name)[1]','nvarchar(40)') name, 
temp.tp.value('(com:FirstName)[1]','nvarchar(40)') firstname,
temp.tp.value('(com:Language/ExternalId)[1]','nchar(10)') Language, 
temp.tp.value('(com:Profession/ExternalId)[1]','int') Profession,
temp.tp.value('for $i in (com:Addresses/com:Address), $j in ($i/Media/ExternalId) where ($j eq "EMAIL") return ($i/com:Address)[1]', 'nvarchar(100)') email

				
from 
@tp.nodes('ThirdPartyReport/ThirdParty') as temp(tp);

Thanks for the reply ;-)

mercredi 25 avril 2012 15:38

Auteur de réponse

FLWOR is like a cursor so can be inefficient, especially over large pieces of XML.

In your example, you're actually using two FLWOR statements. If you just want the first email address then something like this should work:

-- Return first Address item with an ExternalId=EMAIL child
;WITH XMLNAMESPACES ( 'http://www.bsb.com/extraction/common' AS com )
SELECT 
	temp.tp.value('(com:Sending/text())[1]', 'varchar(10)') sending,
	temp.tp.value('(com:Address/text())[1]', 'varchar(max)') email
FROM @tp.nodes('(com:ThirdParty/com:Addresses/com:Address[com:Media/com:ExternalId[.="EMAIL"]])[1]') as temp(tp);

mercredi 25 avril 2012 19:10

0

Thanks for the explanation, although performance is off less importance right now. I first need the correct results, even if it takes some time to finish the query.

I'd have a really extensive xQuery if the predicate is in the from statement though. Email isn't the only data that i have to retrieve, i need a list with client name + contact data eventually.

btw wrapping the whole statement in brackets and adding [1] doesn't work. Do you have any idea what's wrong with my query?
- Réponse
- Citation
mercredi 25 avril 2012 21:14

Auteur de réponse

1

I think the sample XML you provided and the sample query aren't quite right - for example your first query used ThirdPartyReport but the sample XML doesn't have this element, there are no FirstName, Lanugage or Profression elements as per your second query.

Can you post or correct your sample XML, and post up exactly how you want the result to look from that sample XML? Then someone should be able to help.
- Réponse
- Citation

mercredi 25 avril 2012 21:21

Auteur de réponse

Or maybe something along these lines:

DECLARE @tp XML 

SET @tp = '<com:ThirdParty xmlns:com="http://www.bsb.com/extraction/common">
  <com:ExternalId>First ExternalId</com:ExternalId>
  <com:Name>First Name</com:Name>
  <com:Addresses>
    <com:Address>
      <com:Sending>true</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>POST</com:ExternalId>
      </com:Media>
      <com:PostCode>x</com:PostCode>
      <com:Town>x</com:Town>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>email1@email.com</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>FAX</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>email1@email.com</com:Address>
      <com:Media>
        <com:ExternalId>EMAIL</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>PHONE</com:ExternalId>
      </com:Media>
    </com:Address>
    <com:Address>
      <com:Sending>false</com:Sending>
      <com:Address>x</com:Address>
      <com:Media>
        <com:ExternalId>WEB</com:ExternalId>
      </com:Media>
    </com:Address>
  </com:Addresses>
</com:ThirdParty>'

-- Return Address items with an ExternalId=EMAIL child
;WITH XMLNAMESPACES ( 'http://www.bsb.com/extraction/common' AS com )
SELECT 
	temp.tp.value('(com:ExternalId)[1]', 'VARCHAR(MAX)') id,
	temp.tp.value('(com:Name)[1]','nvarchar(40)') name, 
	--temp.tp.value('(com:FirstName)[1]','nvarchar(40)') firstname,
	--temp.tp.value('(com:Language/ExternalId)[1]','nchar(10)') Language, 
	--temp.tp.value('(com:Profession/ExternalId)[1]','int') Profession,
	--temp.tp.value('(com:Sending/text())[1]', 'varchar(10)') sending,

	e.c.value('(com:Address/text())[1]', 'varchar(max)') email

FROM @tp.nodes('com:ThirdParty') as temp(tp)
	CROSS APPLY temp.tp.nodes('(com:Addresses/com:Address[com:Media/com:ExternalId[.="EMAIL"]])[1]') e(c)

Marqué comme réponse ilRe mercredi 25 avril 2012 21:30

mercredi 25 avril 2012 21:30

0

I think the sample XML you provided and the sample query aren't quite right - for example your first query used ThirdPartyReport but the sample XML doesn't have this element, there are no FirstName, Lanugage or Profression elements as per your second query.

Can you post or correct your sample XML, and post up exactly how you want the result to look from that sample XML? Then someone should be able to help.

I will provide a more complete version of the XML tomorrow. Thank you for your efforts so far :).

I think your last query will do actually (although i'd have to study the Cross Apply part, i'm still new to xQuery.)
- Réponse
- Citation