0

I have an SQL Table (Table1) with XML field (Field1).

<row foo1="foo11" foo2="foo12" foo3="foo13" />
<row foo1="foo21" foo2="foo22" foo3="foo23" />
<row foo1="foo31" foo2="foo32" foo3="foo33" />

I need to display data in a table format:

foo1 foo2 foo3
foo11 foo12 foo13
foo21 foo22 foo23
foo31 foo32 foo33

I tried:

 SELECT n.x.value('foo1[1]', 'Varchar(10)') as foo1, 
 n.x.value('foo2[1]', 'Varchar(10)') as foo2, n.x.value('foo3[1]', 
 'Varchar(10)') as foo3
 FROM  Table1
 CROSS APPLY Field1.nodes('/row') n(x)

That is not working. I get table that looks like this:

foo1 foo2 foo3
Null Null Null
Null Null Null
Null Null Null
goryef
  • 1,337
  • 3
  • 22
  • 37
  • Well, first your posted code has several syntax errors in it. We can kinda guess what it _sholud_ be but it would help if you fix what you've posted so we can make sure we're using the same thing you're having an issue with. Second, "That is not working" is not an error description. What is the exact error? Or are the syntax errors what you are having a problem with? – squillman Jun 14 '22 at 18:35
  • @lptr. Is there a way to use column order without names. i.e. column1 instead of foo1? – goryef Jun 14 '22 at 18:54

0 Answers0