CODEWITHSUNDEEP
sql-serverxml
Tommy Sharp
Tommy Sharp

Reputation: 95

T-SQL Return XML values as dataset with rows

I'm struggling with querying a SQL table and returning each row and also splitting out all the "Expressions" xml records into separate rows. I'm tried a few approaches.

This is some test data. I'm hoping to get 2 rows back both id an ID column of 1 and then some columns showing all the attributes of each expression.

CREATE TABLE #MyData (ID INT, MyXML xml)
INSERT INTO #MyData SELECT
1 AS ID
,'<ArrayOfExpressions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Expressions>
    <ExpressionID>1</ExpressionID>
    <FieldName>AlternateDescription</FieldName>
    <Operator>is not filled out</Operator>
    <Condition>777</Condition>
    <AndOr>OR</AndOr>
  </Expressions>
  <Expressions>
    <ExpressionID>2</ExpressionID>
    <FieldName>InputMask</FieldName>
    <Operator>does not contain</Operator>
    <Condition>hello</Condition>
    <AndOr>AND</AndOr>
  </Expressions>
</ArrayOfExpressions>' AS MyXML

And based on some examples this is as far as I got but I'm a bit lost now....

SELECT * FROM 
(select 
       ID
       ,MyXMLRows.ExpressionID.value('(text())[1]', 'varchar(32)') as ExpressionID
from 
       #MyData CROSS APPLY
       MyXML.nodes('/ArrayOfExpressions/Expressions') AS Roles(MyXMLRows)
)  as MyResults

Upvotes: 0

Views: 151

Answers (1)

marc_s
marc_s

Reputation: 754598

Try something like this:

SELECT
    ID,
    ExpressionId = XC.value('(ExpressionID)[1]', 'int'),
    FieldName = XC.value('(FieldName)[1]', 'varchar(50)'),
    Operator = XC.value('(Operator)[1]', 'varchar(50)'),
    Condition = XC.value('(Condition)[1]', 'varchar(50)'),
    AndOr = XC.value('(AndOr)[1]', 'varchar(25)')
FROM
    #MyData
CROSS APPLY
    MyXML.nodes('/ArrayOfExpressions/Expressions') AS XT(XC)

That should return something like this a a result set:

ID ExpressionId FieldName Operator Condition AndOr
1 1 AlternateDescription is not filled out 777 OR
1 2 InputMask does not contain hello AND

Upvotes: 1

Related Questions