CODEWITHSUNDEEP
sqlsql-serverxml
Matt Smucker
Matt Smucker

Reputation: 5244

Pull specific xml node for each row

I need to pull a different <node> for each row in the table, the position of the node is stored in SpecificNode

DECLARE @table TABLE
    (
        XmlValue XML,
        SpecificNode INT
    )

INSERT INTO @table SELECT   '<root><node>Y</node><node>Y</node><node>10</node><node>YARD</node></root>', 3
INSERT INTO @table SELECT   '<root><node>N</node><node>20</node><node>PART</node><node></node><node>PASS</node></root>', 2
INSERT INTO @table SELECT   '<root><node>Y</node><node>30</node><node>FORK</node></root>', 2

I can pull a specified node, but when I try to make it dynamic it give me the error "The argument 1 of the xml data type method "value" must be a string literal."

SELECT  
    XmlValue.value('(/root/node)['+SpecificNode+']', 'VARCHAR(100)')
FROM @table AS tbl

Same error with this

SELECT
    x.value,
    XmlValue.value(x.value, 'VARCHAR(100)')
FROM @table AS tbl
CROSS APPLY (SELECT '(/root/node)['+CONVERT(VARCHAR, SpecificNode)+']' as value) X

My expected output would be

10
20
30

Upvotes: 1

Views: 778

Answers (2)

Jitendra Gupta
Jitendra Gupta

Reputation: 824

Hope this works.

CREATE table #table ( ID INT IDENTITY, XmlValue XML, SpecificNode INT )

INSERT INTO #table SELECT '<root><node>Y</node><node>Y</node><node>10</node><node>YARD</node></root>', 3

INSERT INTO #table SELECT '<root><node>N</node><node>20</node><node>PART</node><node></node><node>PASS</node></root>', 2

INSERT INTO #table SELECT '<root><node>Y</node><node>30</node><node>FORK</node></root>', 2

declare @a varchar(1000), @IDENT INT; declare @values as table (value INT)

SET @IDENT = 1

DECLARE CUR CURSOR FOR select 'SELECT
XmlValue.value(''(/root/node)['+ CONVERT(VARCHAR(10), SpecificNode)+']'', ''VARCHAR(100)'') FROM #table AS tbl WHERE ID = '

from #table

OPEN CUR

FETCH NEXT FROM CUR INTO @A

WHILE @@FETCH_STATUS = 0

BEGIN

SET @A = @A + CONVERT(VARCHAR(10), @IDENT)

INSERT INTO @values(value) EXEC (@A)

SET @IDENT = @IDENT + 1

FETCH NEXT FROM CUR INTO @A

END

CLOSE CUR

DEALLOCATE CUR

DROP TABLE #table SELECT * fROM @values

Some additional efforts needed. First change table variable to temp table, then add ID column to the table with identity. The result will come in different resultsets. To get them all at one resultset,a table variable need to be created to store them.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use sql:column

SELECT
  XmlValue.value('(/root/node[sql:column("SpecificNode")])[1]', 'VARCHAR(100)')
FROM @table AS tbl

Upvotes: 3

Related Questions