Find XML records from table with specific root node in mssql

Question

I have a table with with column, that contains XML. This XML values can have different roots, for example:

<MyAuthenticationParams xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AlsoParams> 
    <SecretKey>MVHXAQA5kF4Ab9siV4vPA4aVPn1EKhbqIBrpCZx2Hg</SecretKey>     <DynamicDescriptor />
  </AlsoParams>
  <myParams>
    <AccountName>Acc1</AccountName>
    <Username>testUsername</Username>
   </myParams>
</MyAuthenticationParams>

or

<Sm1AuthenticationParams xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <AccountName>XGwzJ6RR</AccountName>
  <SomeNumber>123456780</SomeNumber>
</Sm1AuthenticationParams>

How can I select records with root MyAuthenticationParams only?

Ruslan K. · Accepted Answer · 2017-09-06 07:29:23Z

1

This is what you need?

DECLARE @xml1 varchar(8000) = '<MyAuthenticationParams xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AlsoParams> 
    <SecretKey>MVHXAQA5kF4Ab9siV4vPA4aVPn1EKhbqIBrpCZx2Hg</SecretKey>
    <DynamicDescriptor />
  </AlsoParams>
  <myParams>
    <AccountName>Acc1</AccountName>
    <Username>testUsername</Username>
  </myParams>
</MyAuthenticationParams>';

DECLARE @xml2 varchar(8000) = '<Sm1AuthenticationParams xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <AccountName>XGwzJ6RR</AccountName>
  <SomeNumber>123456780</SomeNumber>
</Sm1AuthenticationParams>';

DECLARE @temp TABLE (IsProper bit, XmlData varchar(8000));

INSERT @temp VALUES (1, @xml1), (0, @xml2);

SELECT *
    FROM @temp
    WHERE cast(XmlData AS xml).value('count(/MyAuthenticationParams/*)', 'int') > 0

Output:

IsProper XmlData
-------- ---------------------------
1        <MyAuthenticationParams ...

answered Sep 6, 2017 at 7:29

Ruslan K.

2,0011 gold badge16 silver badges20 bronze badges

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

1 Comment

Вася Пупкин Over a year ago

Thank you! This is what I looked for.

Krishnaraj Gunasekar · Accepted Answer · 2017-09-06 07:08:48Z

It may help you:

declare @xml xml ='<MyAuthenticationParams xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <AlsoParams> 
    <SecretKey>MVHXAQA5kF4Ab9siV4vPA4aVPn1EKhbqIBrpCZx2Hg</SecretKey>     <DynamicDescriptor />
  </AlsoParams>
  <myParams>
    <AccountName>Acc1</AccountName>
    <Username>testUsername</Username>
   </myParams>
</MyAuthenticationParams>'

select 
a.b.query('AlsoParams/SecretKey').value('.','nvarchar(max)')SecretKey,
a.b.query('myParams/AccountName').value('.','nvarchar(max)')AccountName
 from @xml.nodes('MyAuthenticationParams')as a(b)

Or else if xml is stored in colum then try this

select 
a.b.query('AlsoParams/SecretKey').value('.','nvarchar(max)')SecretKey,
a.b.query('myParams/AccountName').value('.','nvarchar(max)')AccountName
 from yourtable cross apply xmlcolumn.nodes('MyAuthenticationParams')as a(b)

Collectives™ on Stack Overflow

Find XML records from table with specific root node in mssql

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related