SQL XML query from column

Question

How to select file names from this kind of XML in a SQL Server column?

file name is in attribute VALUE only if high level tag is object with name File like this

<Object NAME="File">
    <Parameter ID="1" NAME="Name" VALUE="\\mysvr\fiels\readme1.txt" />
</Object>

This sub-hierarchy can be placed on any level of xml (see example below)

XML column can contain 0-N file names, I need list like this:

id, filename
--- ------------------------
 1  \\mysvr\fiels\readme1.txt
 1  \\mysvr\fiels\readme2.txt
 2  \\mysvr\fiels\readme3.txt
 2  \\mysvr\fiels\readme4.txt

Example of XML contents:

declare @t1 table
(   id int,
   x XML
)

insert into @t1 (id, x)
select 1,N'<root name="name" id="12">
<class1>
  <Object NAME="File">
              <Parameter ID="1" NAME="Name" VALUE="\\mysvr\fiels\readme1.txt" />
   </Object>
</class1>
<class1>
<subclass1>
<Object NAME="File">
              <Parameter ID="10" NAME="Name" VALUE="\\mysvr\fiels\readme2.txt" />
</Object>
<Object NAME="bitmap">
              <Parameter ID="11" NAME="my1" VALUE="bmp" />
</Object>
</subclass1>
</class1>
</root>'
union 
select 2,N'<root name="name" id="12">
<class1>
  <Object NAME="File">
              <Parameter ID="13" NAME="Name" VALUE="\\mysvr\fiels\readme3.txt" />
  </Object>
  <Object NAME="Font">
              <Parameter ID="22" NAME="Tahoma" VALUE="11" />
  </Object>
</class1>
<class1>
<subclass1>
  <Object NAME="File">
              <Parameter ID="14" NAME="Name" VALUE="\\mysvr\fiels\readme4.txt" />
  </Object>
</subclass1>
</class1>
</root>'

marc_s · Accepted Answer · 2012-11-06 12:15:52Z

1

Try this:

SELECT
    id,
    Filename = ObjPrm.value('@VALUE', 'varchar(100)')
FROM @t1
CROSS APPLY x.nodes('//Object[@NAME="File"]/Parameter') AS Tbl(ObjPrm)

Gives me an output of:

enter image description here

answered Nov 6, 2012 at 12:15

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Comments

Ruan · Accepted Answer · 2012-11-06 11:07:37Z

0

To do this you can do the following.

Define a string of the specific details you want to display.

You want to display id and Name so your string will look something like this.

String holder = "#id# -- #VALUE#"

Then you search through that whole string for the values you have between the two '#' signs.

When you find them, just do a search for the value inside the Quotation marks, and display them.

answered Nov 6, 2012 at 11:07

Ruan

4,32411 gold badges68 silver badges92 bronze badges

1 Comment

ZedZip Over a year ago

I need t-sql query. These xml fields are in table and I need to select ... from @t1 and receive recordset: id and filenames (see the example)

Collectives™ on Stack Overflow

SQL XML query from column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related