Find XML with specific parent node in mssql

Question

I've got table, which contains XML-type column in it. Some of the values in this column begin with

<MyAuthenticationParams>
.....
</MyAuthenticationParams>

And some with

<Sm1AuthenticationParams>
.......
</Sm1AuthenticationParams>

How can I select only records with parent node MyAuthenticationParams? Thanks for any help.

EDIT: This is how XML looks like

    <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 like this

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

Could you show more of the XML structure?

gotqn
– gotqn

2017-09-01 08:23:31 +00:00
Commented Sep 1, 2017 at 8:23 — gotqn
– gotqn, Commented Sep 1, 2017 at 8:23

gotqn · Accepted Answer · 2017-09-01 08:31:43Z

0

You can try this:

DECLARE @DataSource XML =
N'
<DataSource>
<parentA>
    <child1></child1>
    <child2></child2>
    <child3></child3>
</parentA>
<parentB>
    <child4></child4>
    <child5></child5>
    <child6></child6>
</parentB>
<parentA>
    <child7></child7>
    <child8></child8>
</parentA>
</DataSource>';


SELECT P.c.query('.')
      ,T.c.query('.')
FROM @DataSource.nodes('DataSource/parentA') P(c)
OUTER APPLY P.c.nodes('*') T(c);

The idea is to use nodes to get all parentA nodes (in your case these are the MyAuthenticationParams nodes). Then, with second nodes we are getting their children but you can whatever you Want from here.

answered Sep 1, 2017 at 8:31

gotqn

44k47 gold badges167 silver badges257 bronze badges

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

Comments

Amit Kumar Singh · Accepted Answer · 2017-09-01 12:35:38Z

0

SELECT
a.b.value('NODE_ONE[1]','nvarchar(max)') AS Node_One,
a.b.value('NODE_TWO[1]','nvarchar(max)') AS Node_Two
FROM @MyXML.nodes('XML_MAIN_NODE/MyAuthenticationParams') a(b)

Since your XML is in table, use below.

SELECT
a.b.value('NODE_ONE[1]','nvarchar(max)') AS Node_One,
a.b.value('NODE_TWO[1]','nvarchar(max)') AS Node_Two
FROM Plans s CROSS APPLY s.Params.nodes('XML_MAIN_NODE/MyAuthenticationParams') a(b)

In your case, this works. PATH_TILL_MyAuthenticationParams is the path till the parent node of MyAuthenticationParams tag. So, if XML is like <root><nodeA><MyAuthenticationParams>...<MyAuthenticationParams><Other_tag></Other_tag></nodeA></root>, PATH_TILL_MyAuthenticationParams is 'root/nodeA'

DECLARE @xml XML
select @xml = a.b.query('*') FROM Plans s CROSS APPLY s.Params.nodes('PATH_TILL_MyAuthenticationParams') a(b)
PRINT CONVERT(NVARCHAR(MAX), @xml)

DECLARE @accName NVARCHAR(500)
DECLARE @userName NVARCHAR(500)
DECLARE @secretKey NVARCHAR(2000)
SELECT
@accName = a.b.value('AccountName[1]','nvarchar(max)'),
@userName = a.b.value('Username[1]','nvarchar(max)')
FROM @xml.nodes('MyAuthenticationParams/myParams') a(b)

PRINT @accName
PRINT @userName

SELECT
@secretKey = a.b.value('SecretKey[1]','nvarchar(max)')
FROM @xml.nodes('MyAuthenticationParams/AlsoParams') a(b)
PRINT @secretKey

Edit: In a Single Query,

DECLARE @AccountName NVARCHAR(500) = ''
DECLARE @Username NVARCHAR(500) = ''
DECLARE @Key VARCHAR(500) = ''

    SELECT @AccountName = x.u.value('(/ROOT_NODE/.../MyAuthenticationParams/myParams/AccountName)[1]', 'nvarchar(max)')
        , @Username = x.u.value('(/ROOT_NODE/.../MyAuthenticationParams/myParams/Username)[1]', 'nvarchar(max)'),
        @Key = x.u.value('(/ROOT_NODE/.../MyAuthenticationParams/AlsoParams/SecretKey)[1]', 'nvarchar(max)')
    FROM Plans a  CROSS APPLY a.Params.nodes('ROOT_NODE') x(u)

    PRINT @AccountName
    PRINT @Username
    PRINT @Key

edited Sep 1, 2017 at 12:35

answered Sep 1, 2017 at 8:24

Amit Kumar Singh

4,4952 gold badges11 silver badges24 bronze badges

8 Comments

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

What is a and b in this context?

Amit Kumar Singh Over a year ago

It's a table valued method. The table (and its columns) returned by a table-valued method need to be aliased. a is for table it creates. c is for column.

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

I don't quite understand, how can I use it to table. For example, my table name is Plans and column with xml is Params. Should I declare it to vars somwhere?

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

thank you for that, but it's not working. It returns empty columns

Amit Kumar Singh Over a year ago

share your xml in the question.

|

Collectives™ on Stack Overflow

Find XML with specific parent node in mssql

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related