Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
xQuery Error: query returns multiple nodes even with [1] predicate

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

  • Wednesday, April 25, 2012 12:06 PM
     
      Has Code
    Sign In to Vote
    0

    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?


     

All Replies

  • Wednesday, April 25, 2012 1:10 PM
    Answerer
     
      Has Code
    Sign In to Vote
    1

    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);

     
  • Wednesday, April 25, 2012 1:42 PM
     
      Has Code
    Sign In to Vote
    0

    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 ;-)

     
  • Wednesday, April 25, 2012 3:38 PM
    Answerer
     
      Has Code
    Sign In to Vote
    1

    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);

     
  • Wednesday, April 25, 2012 7:10 PM
     
     
    Sign In to Vote
    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?


     
  • Wednesday, April 25, 2012 9:14 PM
    Answerer
     
     
    Sign In to Vote
    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.

     
  • Wednesday, April 25, 2012 9:21 PM
    Answerer
     
     Answered Has Code
    Sign In to Vote
    1

    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)

    • Marked As Answer by ilRe Wednesday, April 25, 2012 9:30 PM
    •  
     
  • Wednesday, April 25, 2012 9:30 PM
     
     
    Sign In to Vote
    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.)