XML SQL Server querying to retrieve attribute value and tag text value

Question

I'm new to querying XML in SQL Server, this is not one of my daily tasks, I'm helping a colleague. For example I have the following XML stored on a column in SQL:

How can I retrieve two columns, one having the id value and the other one, the value from the next inside tag (eg.:2109 and 0 from <Bool>0</Bool>)

Desired output:

id  value
2109    0
341     2
342     10
2196    753064REPJ1
1283    2
1293    0_Imprumutat

Thx!

<Item id="645" flags="769">
  <Row>
    <Item id="2109" flags="257">
      <Bool>0</Bool>
    </Item>
    <Item id="341" flags="257">
      <Str>2</Str>
    </Item>
    <Item id="342" flags="257">
      <Str>10</Str>
    </Item>
    <Item id="2196" flags="257">
      <Str>753064REPJ1</Str>
    </Item>
    <Item id="1283" flags="257">
      <Row>
        <Str>2</Str>
      </Row>
    </Item>
    <Item id="1293" flags="257">
      <Row>
        <Str>0_Imprumutat</Str>
      </Row>
    </Item>
    <Item id="1251" flags="257">
      <Str>2079759</Str>
    </Item>
    <Item id="2101" flags="257">
      <Dbl>500000</Dbl>
    </Item>
    <Item id="343" flags="257">
      <Str>2</Str>
    </Item>
    <Item id="2065" flags="257">
      <Dbl>1000000</Dbl>
    </Item>
    <Item id="2098" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2102" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2100" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2099" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="359" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="361" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="347" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="2346" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="1912" flags="513">
      <Str>1234</Str>
    </Item>
    <Item id="2219" flags="513">
      <Str>1234</Str>
    </Item>
    <Item id="356" flags="513">
      <Int>1999</Int>
    </Item>
    <Item id="2218" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2220" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2702" flags="513">
      <Date>2016-10-02Z</Date>
    </Item>
    <Item id="377" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="1218" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1219" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1596" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="351" flags="513">
      <Str>Adresa</Str>
    </Item>
    <Item id="352" flags="513">
      <Str>B3</Str>
    </Item>
    <Item id="353" flags="513">
      <Str>LOc</Str>
    </Item>
    <Item id="354" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="355" flags="513">
      <Str>jfdkls</Str>
    </Item>
    <Item id="1288" flags="513">
      <Int>3</Int>
    </Item>
    <Item id="368" flags="513">
      <Int>9</Int>
    </Item>
    <Item id="1887" flags="513">
      <Str>Realtech Services SRL</Str>
    </Item>
    <Item id="370" flags="513">
      <Date>2015-10-02Z</Date>
    </Item>
    <Item id="375" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="371" flags="513">
      <Str>Asirom</Str>
    </Item>
    <Item id="372" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="373" flags="513">
      <Str>RON</Str>
    </Item>
    <Item id="374" flags="513">
      <Date>2016-01-05Z</Date>
    </Item>
  </Row>
  <Row>
    <Item id="2109" flags="257">
      <Bool>0</Bool>
    </Item>
    <Item id="341" flags="257">
      <Str>1</Str>
    </Item>
    <Item id="342" flags="257">
      <Str>1</Str>
    </Item>
    <Item id="2196" flags="257">
      <Str>753064REPF2</Str>
    </Item>
    <Item id="1283" flags="257">
      <Row>
        <Str>2</Str>
      </Row>
    </Item>
    <Item id="1293" flags="257">
      <Row>
        <Str>0_Imprumutat</Str>
      </Row>
    </Item>
    <Item id="1251" flags="257">
      <Str>2079759</Str>
    </Item>
    <Item id="2101" flags="257">
      <Dbl>500000</Dbl>
    </Item>
    <Item id="343" flags="257">
      <Str>1</Str>
    </Item>
    <Item id="2065" flags="257">
      <Dbl>1000000</Dbl>
    </Item>
    <Item id="2098" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2102" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2100" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2099" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="347" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="2346" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="1912" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2219" flags="513">
      <Str>1236</Str>
    </Item>
    <Item id="356" flags="513">
      <Int>1999</Int>
    </Item>
    <Item id="2218" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2702" flags="513">
      <Date>2016-01-02Z</Date>
    </Item>
    <Item id="377" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="1218" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1219" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1596" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="351" flags="513">
      <Str>jgfkdl</Str>
    </Item>
    <Item id="352" flags="513">
      <Str>B3</Str>
    </Item>
    <Item id="353" flags="513">
      <Str>gfdgfd</Str>
    </Item>
    <Item id="354" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="355" flags="513">
      <Str>gfdgfd</Str>
    </Item>
    <Item id="357" flags="513">
      <Int>9</Int>
    </Item>
    <Item id="1288" flags="513">
      <Int>3</Int>
    </Item>
    <Item id="368" flags="513">
      <Int>9</Int>
    </Item>
    <Item id="1887" flags="513">
      <Str>Euroeval SRL</Str>
    </Item>
    <Item id="370" flags="513">
      <Date>2015-09-02Z</Date>
    </Item>
    <Item id="375" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="371" flags="513">
      <Str>Allianz-Tiriac</Str>
    </Item>
    <Item id="372" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="373" flags="513">
      <Str>RON</Str>
    </Item>
    <Item id="374" flags="513">
      <Date>2017-10-28Z</Date>
    </Item>
  </Row>
  <Row>
    <Item id="2109" flags="257">
      <Bool>0</Bool>
    </Item>
    <Item id="341" flags="257">
      <Str>1</Str>
    </Item>
    <Item id="342" flags="769">
      <Str>14</Str>
    </Item>
    <Item id="2196" flags="257">
      <Str>753064REPF3</Str>
    </Item>
    <Item id="1283" flags="257">
      <Row>
        <Str>2</Str>
      </Row>
    </Item>
    <Item id="1293" flags="257">
      <Row>
        <Str>0_Imprumutat</Str>
      </Row>
    </Item>
    <Item id="1251" flags="257">
      <Str>2079759</Str>
    </Item>
    <Item id="2101" flags="257">
      <Dbl>500000</Dbl>
    </Item>
    <Item id="343" flags="257">
      <Str>1</Str>
    </Item>
    <Item id="2065" flags="257">
      <Dbl>1000000</Dbl>
    </Item>
    <Item id="2100" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2099" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2102" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="2098" flags="257">
      <Dbl>0</Dbl>
    </Item>
    <Item id="359" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="361" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="347" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="2346" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="1912" flags="513">
      <Str>12346</Str>
    </Item>
    <Item id="2219" flags="513">
      <Str>54</Str>
    </Item>
    <Item id="356" flags="513">
      <Int>1999</Int>
    </Item>
    <Item id="2218" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2220" flags="513">
      <Str>123</Str>
    </Item>
    <Item id="2702" flags="513">
      <Date>2016-10-02Z</Date>
    </Item>
    <Item id="377" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="1218" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1219" flags="513">
      <Int>0</Int>
    </Item>
    <Item id="1596" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="351" flags="513">
      <Str>iuyiy</Str>
    </Item>
    <Item id="352" flags="513">
      <Str>B2</Str>
    </Item>
    <Item id="353" flags="513">
      <Str>kjhlhj</Str>
    </Item>
    <Item id="354" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="355" flags="513">
      <Str>lkjhljh</Str>
    </Item>
    <Item id="357" flags="513">
      <Int>9</Int>
    </Item>
    <Item id="1288" flags="513">
      <Int>1</Int>
    </Item>
    <Item id="368" flags="513">
      <Int>9</Int>
    </Item>
    <Item id="1887" flags="513">
      <Str>Euroeval SRL</Str>
    </Item>
    <Item id="370" flags="513">
      <Date>2016-06-02Z</Date>
    </Item>
    <Item id="375" flags="513">
      <Int>2</Int>
    </Item>
    <Item id="371" flags="513">
      <Str>Uniqa</Str>
    </Item>
    <Item id="372" flags="513">
      <Int>1000000</Int>
    </Item>
    <Item id="373" flags="513">
      <Str>RON</Str>
    </Item>
    <Item id="374" flags="513">
      <Date>2016-02-21Z</Date>
    </Item>
  </Row>
</Item>

it would be great if you post here some sample output what you want — wiretext
– wiretext, Commented Oct 1, 2015 at 8:37

Sateesh Pagolu · Accepted Answer · 2015-10-01 08:33:14Z

2

You need to use XQuery, outer apply and give path to your target node.

Final Query

SELECT  n.a.value('@id','int') as id,n.a.value('(Bool)[1]','bit')
from xmldata x
        outer apply x.data.nodes('Item/Row/Item')as n(a)
where n.a.value('(Bool)[1]','varchar(max)') is not null

Explanation:

After navigating to desired node, use n.a.value('@Attribute','datatype') syntax to fetch required attribute,n.a.value('(ElementName)[1]','datatype') syntax to fetch element value.

edited Oct 1, 2015 at 8:33

answered Oct 1, 2015 at 8:26

Sateesh Pagolu

9,6563 gold badges32 silver badges49 bronze badges

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

Comments

Gottfried Lesigang · Accepted Answer · 2015-10-06 16:29:59Z

You can use sql:variable() or sql:column() (within an XPATH expression). I declared an XML-variable "@x" and set it to the XML given above.

With this you get exactly what you want:

DECLARE @outerID INT=645;
DECLARE @innerID INT=2109;

SELECT @x.value('(/Item[@id=sql:variable("@outerID")]/Row[1]/Item[@id=sql:variable("@innerID")]/Bool)[1]','bit')

If you need it for all rows-nodes in your XML you can go like this:

DECLARE @id INT=2109;
SELECT Item.OneRow.value('(*[@id=sql:variable("@id")])[1]','bit')
FROM @x.nodes('/Item/Row') AS Item(OneRow)

This statement will extract all ids, first child's name and value together with ther Row-number:

;WITH TheRows AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowInx
          ,AllRows.OneRow.query('.') AS RowXML
    FROM @x.nodes('/Item/Row') AS AllRows(OneRow) 
)
SELECT RowInx
      ,AllRowItems.OneItem.value('@id','int') AS id
      ,AllRowItems.OneItem.value('@flags','int') AS flags
      ,AllRowItems.OneItem.query('fn:local-name(./*[1])') AS elementName
      ,AllRowItems.OneItem.value('(./*)[1]','varchar(max)') AS elementValue
FROM TheRows
CROSS APPLY RowXML.nodes('/Row/Item') AS AllRowItems(OneItem)

Collectives™ on Stack Overflow

XML SQL Server querying to retrieve attribute value and tag text value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related