Question: I'm trying to reformat the value in a sql xml column using sqlserver xquery syntax.

I would like that for each **Genre** tag a **"G"** is written in an string, and for each **Book** inside *Genre* depending if the book has a **Lost** tag an **"\_"** if absend or a **"."** if it is available, not taking the value into account.
The output-string has to be in exact the same order as the xml.

This is my input xml

```
<Lib>
  <Genre name=Horror>
     <Book>
        <Title>1</Title>
     </Book>
     <Book>
        <Title>2</Title>
        <Lost>1</Lost>
     </Book>
     <Book>
        <Title>3</Title>
     </Book>
  </Genre>
  <Genre name=Romance>
     <Book>
        <Title>5</Title>
     </Book>
     <Book>
        <Title>6</Title>
        <Lost>0</Lost>
     </Book>
  </Genre>
</Lib>

```

The output I would like to achieve is:

```
G_._G_.

```

Answer: 
One solution is to loop over genres and books to construct the result:

```
for $genre in //Genre
return (
  "G",
  for $book in $genre/Book
  return (
    if ($book/Lost)
    then "."
    else "_"
  )
)

```

*([Try on sqlfiddle.](http://sqlfiddle.com/#!6/d41d8/13783/0))*

As you need to return the letters in document order you could alternatively loop over all node taking a sax-like approach:

```
for $node in //*
return (
  "G"[$node/self::Genre],
  "."[$node/self::Book[Lost]],
  "_"[$node/self::Book[not(Lost)]]
)

```

*([Try on sqlfiddle.](http://sqlfiddle.com/#!6/d41d8/13782/0))*

Remember that the serialization of a node sequence is implementation dependent, if there's any unwanted whitespace there might be an option to omit it. Alternatively, wrap it into `string-join($sequence, '')`.
First, you can't get away with badly formatted XML in SQL Server.
Part `<Genre name=Horror>` is incorrrect, you have to write `<Genre name="Horror">`

See this:

```
DECLARE @lib xml = 
'<Lib>
  <Genre name="Horror">
     <Book>
        <Title>1</Title>
     </Book>
     <Book>
        <Title>2</Title>
        <Lost>1</Lost>
     </Book>
     <Book>
        <Title>3</Title>
     </Book>
  </Genre>
  <Genre name="Romance">
     <Book>
        <Title>5</Title>
     </Book>
     <Book>
        <Title>6</Title>
        <Lost>0</Lost>
     </Book>
  </Genre>
</Lib>'

```

If you omit quotation marks, an error will be returned.

Another thing is that this is probably job for application logic. However, if you don't have application layer and youo need this king of... report(?) then you can use the following code. Pay attention that `@lib` variable is used here.

```
DECLARE @output nvarchar(max) 

;WITH numberedBook AS (
    SELECT
        GenreName,
        RowNumber = ROW_NUMBER() OVER(PARTITION BY GenreName ORDER BY (SELECT NULL)),
        BookTitle,
        BookLost
    FROM 
        @lib.nodes('Lib/Genre/Book') T(c)
        CROSS APPLY(
            SELECT 
                GenreName = T.c.value('../@name', 'nvarchar(255)'),
                BookTitle = T.c.value('./Title[1]', 'nvarchar(255)'),
                BookLost = T.c.value('./Lost[1]', 'nvarchar(255)')
        ) lib
)
SELECT @output = 
    COALESCE(@output + '', '') + 
        CASE WHEN RowNumber = 1 THEN 'G' ELSE '' END +
        CASE WHEN BookLost IS NULL THEN '_' ELSE '.' END
FROM numberedBook

SELECT @output

```