Display xml elements as multiple rows in a view

Prev: Finding Duplicate Tests - Please Help
Next: truncate table question

From: wdudek on 25 Jun 2008 11:10

---

Is it possible to display elements from xml stored in a table as multiple
rows in a view?

For example given the following xml

<IsbnResults>
<Result>
<Isbn14>09780671024239</Isbn14>
</Result>
<Result>
<Isbn14>09780671024246</Isbn14>
</Result>
<Result>
<Isbn14>09780684856476</Isbn14>
</Result>
<Result>
<Isbn14>09780671779108</Isbn14>
</Result>
<Result>
<Isbn14>09780671779207</Isbn14>
</Result>
</IsbnResults>

Is there a syntax I could use in a view that would give me 1 column with the
Isbn14 field and it's data like below? I have been able to use the following
xquery statement to get it as 1 row with all the values but I need each value
in it's own row. .query('data(IsbnResults/Result/Isbn14)')

Isbn14
09780671024239
09780671024246
09780684856476
09780671779108
09780671779207

Thanks
Bill

From: Plamen Ratchev on 25 Jun 2008 11:33

---

Here is one way:

DECLARE @xml XML;

SET @xml =
N'<IsbnResults>
<Result>
<Isbn14>09780671024239</Isbn14>
</Result>
<Result>
<Isbn14>09780671024246</Isbn14>
</Result>
<Result>
<Isbn14>09780684856476</Isbn14>
</Result>
<Result>
<Isbn14>09780671779108</Isbn14>
</Result>
<Result>
<Isbn14>09780671779207</Isbn14>
</Result>
</IsbnResults>';

SELECT T.isbn.value('(Isbn14)[1]', 'VARCHAR(14)') AS isbn
FROM @xml.nodes('IsbnResults/Result') AS T(isbn);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Mark on 25 Jun 2008 11:35

---

Something like this?

CREATE VIEW myview
AS
SELECT r.value('.','varchar(20)') AS Isbn14
FROM mytable
CROSS APPLY mytable.xmlCol.nodes('IsbnResults/Result/Isbn14') as x(r)

From: wdudek on 25 Jun 2008 11:51

---

Thanks, That answered it. Unfortunely the logic started to become too complex
to get to the results so I am going to have to re-work my original plan.

Thanks agin

"Plamen Ratchev" wrote:

> Here is one way:
>
> DECLARE @xml XML;
>
> SET @xml =
> N'<IsbnResults>
> <Result>
> <Isbn14>09780671024239</Isbn14>
> </Result>
> <Result>
> <Isbn14>09780671024246</Isbn14>
> </Result>
> <Result>
> <Isbn14>09780684856476</Isbn14>
> </Result>
> <Result>
> <Isbn14>09780671779108</Isbn14>
> </Result>
> <Result>
> <Isbn14>09780671779207</Isbn14>
> </Result>
> </IsbnResults>';
>
> SELECT T.isbn.value('(Isbn14)[1]', 'VARCHAR(14)') AS isbn
> FROM @xml.nodes('IsbnResults/Result') AS T(isbn);
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

 | 
Pages: 1
Prev: Finding Duplicate Tests - Please Help
Next: truncate table question