Prev: Merging two select cases
Next: The multi-part identifier "Goal.SalesPersonId" could not be bound.
From: <a> on 2 Dec 2009 12:47
Want to change a query from openxml to use xml data type but the parameter
is element centric and the query returns null.

DECLARE @authorsXML XML
SET @authorsXML = '
<Authors>
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>
</Authors>
'
select t.c.value('@ID','varchar(100)')
from @authorsXML.nodes('/Authors/Author') t(c)

--- this one works

DECLARE @authorsXML varchar(max)
SET @authorsXML = '
<Authors>
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>
</Authors>
'
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @authorsXML
select * from OPENXML (@DocHandle, '/Authors/Author', 2) WITH (ID
VARCHAR(50)) AS xmlRole
EXEC sp_xml_removedocument @docHandle


From: Plamen Ratchev on 2 Dec 2009 13:07
The query you have is for attribute centric XML. Here is how you can parse element centric XML:

SELECT t.c.value('(ID)[1]','varchar(100)')
FROM @authorsXML.nodes('/Authors/Author') t(c);

--
Plamen Ratchev
http://www.SQLStudio.com
From: <a> on 2 Dec 2009 13:31
That worked, great! Can you please explain to me what is a singleton then?
thanks

Per static typing requirements, [1] is added at the end of the path
expression in the value() method to explicitly indicate that the path
expression returns a singleton.


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:g5adnUPQtMFJMYvWnZ2dnUVZ_qli4p2d(a)speakeasy.net...
> The query you have is for attribute centric XML. Here is how you can parse
> element centric XML:
>
> SELECT t.c.value('(ID)[1]','varchar(100)')
> FROM @authorsXML.nodes('/Authors/Author') t(c);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 2 Dec 2009 13:58
In XML you can have multiple elements at the same level, like:

<root>
<author>Joe</author>
<author>Jeff</author>
</root>

Since the value XQuery method returns a scalar value, you have to specify the position of the element you want to
retrieve (in this case [1]). Even if your XML has a single element at the level (like it is in your example), you still
have to use the numeric predicate to guarantee a scalar value is returned.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Merging two select cases
Next: The multi-part identifier "Goal.SalesPersonId" could not be bound.