CODEWITHSUNDEEP
sql-serverxml
Вася Пупкин
Вася Пупкин

Reputation: 153

Find XML records from table with specific root node in mssql

Follow-up of this 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?

Upvotes: 2

Views: 54

Answers (2)

Ruslan K.
Ruslan K.

Reputation: 1981

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 ...

Upvotes: 1

Krishnaraj Gunasekar
Krishnaraj Gunasekar

Reputation: 395

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)

Upvotes: 1

Related Questions