CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Krishnaraj Gunasekar
Krishnaraj Gunasekar

Reputation: 395

xml to tsql select statement returns only one row

I need to build an table using an xml.

This is my xml:

<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>

My try:

DECLARE @ixml INT,
@Param  VARCHAR(max)='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'


 EXEC sp_xml_preparedocument  @ixml OUTPUT, @Param  

    Select Child,ID
    FROM OPENXML(@ixml, 'root')
    WITH 
    (
        Child [nVARCHAR](max) 'Child',  
        ID [INT] 'ID'
    ) 


----------

Actual output :

Child  |    ID
c1     |    9908

Expected Output:

Child  |    ID
c1     |    9908
c2     |    9908
c3     |    9908
c4     |    9908

can anyone help me?

Upvotes: 1

Views: 661

Answers (2)

TT.
TT.

Reputation: 16137

Starting with declaring the @Param as an XML type, and using XPath expressions in nodes and value, you would get:

DECLARE @Param XML='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'

SELECT
    Child=n.v.value('.[1]','NVARCHAR(128)'),
    ID=n.v.value('../ID[1]','INT')
FROM
    @Param.nodes('root/Child') AS n(v);

With the expected result.

A slightly better version (with input from @Shnugo), with direct selection of /root/ID instead of backward navigation in the XPath expression:

SELECT
    Child=n.v.value('.[1]','NVARCHAR(128)'),
    [email protected]('(/root/ID/text())[1]','INT')
FROM
    @Param.nodes('root/Child') AS n(v);

Upvotes: 3

Tyron78
Tyron78

Reputation: 4187

Try this:

DECLARE @ixml INT,
@Param  VARCHAR(max)='<root>
  <Child>c1</Child>
  <Child>c2</Child>
  <Child>c3</Child>
  <Child>c4</Child>
  <ID>9908</ID>
</root>'


 EXEC sp_xml_preparedocument  @ixml OUTPUT, @Param  

Select Child, ID
FROM OPENXML(@ixml, '/root/Child')
WITH 
(
    Child [nVARCHAR](max) '.', ID [int]   '../ID'
)

Upvotes: 4

Related Questions