Insert values into a table from XML (SQL Server)

Question

I have this XML:

<Files>
  <File>
    <Name>pic1</Name> 
    <Description>Pics</Description> 
    <URL>URL1</URL> 
    <TypeText>jpg</TypeText> 
  </File>
  <File>
    <Name>pic2</Name> 
    <Description>Pics</Description> 
    <URL>URL2</URL> 
    <TypeText>jpg</TypeText> 
  </File>
  <File>
    <Name>pic3</Name> 
    <Description>Pics</Description> 
    <URL>URL3</URL> 
    <TypeText>jpg</TypeText> 
  </File>
</Files>

And I want to insert them in an SQL Server table. I use this code:

INSERT INTO #T
SELECT
    XFiles.value('/Name', 'nvarchar(120)'),
    XFiles.value('/Description', 'nvarchar(300)'),
    XFiles.value('/URL', 'nvarchar(max)'),
    XFiles.value('/TypeText', 'nvarchar(120)')
FROM @FilesXML.nodes('/Files/File')
AS XTbl(XFiles)

but it gives me error.

XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I think I'm making a mistake with the jpath part. Is it right or wrong?

Thanx in advance

What is the error? You should provide the error here.

lowselfesteemsucks
– lowselfesteemsucks

2014-07-29 11:11:10 +00:00
Commented Jul 29, 2014 at 11:11 — lowselfesteemsucks
– lowselfesteemsucks, Commented Jul 29, 2014 at 11:11
@Ivica Sorry, Post Updated :)

Payam Sh
– Payam Sh

2014-07-29 11:20:23 +00:00
Commented Jul 29, 2014 at 11:20 — Payam Sh
– Payam Sh, Commented Jul 29, 2014 at 11:20

adhocgeek · Accepted Answer · 2014-07-29 11:47:23Z

3

You need to alter your select query :

SELECT
    XFiles.value('Name[1]', 'nvarchar(120)') as Name,
    XFiles.value('Description[1]', 'nvarchar(300)'),
    XFiles.value('URL[1]', 'nvarchar(max)'),
    XFiles.value('TypeText[1]', 'nvarchar(120)')
FROM @FilesXML.nodes('/Files/File') as XTbl(XFiles)

If you just select Name, for example, that XPath query returns a collection of Name nodes - even if there's only one child node! - hence the error you're seeing. You have to ensure that the XPath query returns only a single thing, hence adding the [1] indexer.

edited Jul 29, 2014 at 11:47

answered Jul 29, 2014 at 11:23

adhocgeek

1,4771 gold badge16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert values into a table from XML (SQL Server)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related