CODEWITHSUNDEEP
sqlsql-serverxmlxquery
Niko Dierickx
Niko Dierickx

Reputation: 161

SQL query xml to text reformatted

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_.

Upvotes: 0

Views: 66

Answers (2)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

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

Upvotes: 1

Jens Erat
Jens Erat

Reputation: 38662

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

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

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, '').

Upvotes: 2

Related Questions