How to return multiple columns from FLWOR

I have very simple xml shredding requirement. What I would like to do is to get value of SourcesID and SourcesType returned as columns from the following. Currently I could get both values concatenated but requirement dictate having both values returned as separate columns. Please notice that given statement is a simplified version and I understand using Value function would be an easier solution but I have set of conditions within for that needs to be evaluated before I select SourcesID node. So, I would appreciate if the solution proposed is along the line of given code.

DECLARE @.xml XML

SET @.xml =

'<State>

<SourceState>

<SourcesID>57341</SourcesID>

<SourcesType>50</SourcesType>

</SourceState>

</State>'

SELECT

Element.Val.query(

'for $s in self::node()

return $s//SourcesID/text(),

for $s in self::node()

return $s//SourcesType/text()

') AS SourcesID

FROM @.xml.nodes('/State') AS Element(Val)

Asaf:

I think that I still don't understand what you are looking for in terms of output. Here is my first pass at trying to understand your needs. Please comment so that I might be able to properly target this.

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
select parsename(cast(SourcesID as varchar), 2) as SourcesID,
substring(parseName(cast(SourcesID as varchar), 1), 2, 99)
as SourcesType
from ( SELECT
Element.Val.query(
'for $s in self::node()/SourceState/SourcesID/text()
return concat(string($s),"."),
for $s in self::node()/SourceState/SourcesType/text()
return string($s)
') AS SourcesID
FROM @.xml.nodes('/State') AS Element(Val)
) x

/*
SourcesID SourcesType
-- -
57341 50
*/

|||

Hi there,

I am sorry for having a bit confusing question but the proposed solution from you was what I needed. What I was hoping, though, was to basically get two columns returned from FLWOR without any concatenation and parsing of returned resultset later on.

Thanks very much.

|||

It is not clear what kind of condition you have but note that you can also apply conditions in path expressions in square brackets so perhaps your problem can be solved by simply applying the condition already when you use the nodes function e.g.

FROM @.xml.nodes('/State[SourceState/SourcesType > 40]') AS Element(Val)

then you can apply the value method to get two columns in the result.

|||

Code Snippet

DECLARE @.xml XML
SET @.xml =
'<State>
<SourceState>
<SourcesID>57341</SourcesID>
<SourcesType>50</SourcesType>
</SourceState>
</State>'
SELECT
Element.Val.query(
'for $s in self::node()
return $s//SourcesID/text()') as a,
Element.val.query(
'for $s in self::node()
return $s//SourcesType/text()') as b
FROM @.xml.nodes('/State') AS Element(Val)

/*
a b
-
57341 50
*/