I have the following XML and i need to store it in a sql table [closed]

i need the tags ServerName, DBName, UserName, RoleName. I can not access to these tags, because the first part of the xml is creating me troubles

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Selected.System.Object</T>
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">123Z</S>
      <S N="RoleName">test1</S>
    </MS>
  </Obj>
  <Obj RefId="1">
   <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">1234</S>
      <S N="RoleName">test2</S>
    </MS>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">12345aa</S>
      <S N="RoleName">test3</S>
    </MS>
   </Obj>
</Objs>

Answer

In your XML there are two different structures:

DECLARE @xml XML=
N'<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Selected.System.Object</T>
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">123Z</S>
      <S N="RoleName">test1</S>
    </MS>
  </Obj>
  <Obj RefId="1">
   <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">1234</S>
      <S N="RoleName">test2</S>
    </MS>
  </Obj>
  <Obj RefId="2">
    <TNRef RefId="0" />
    <MS>
      <S N="ServerName">DDW</S>
      <S N="DBName">dwSeriesOLAP</S>
      <S N="UserName">12345aa</S>
      <S N="RoleName">test3</S>
    </MS>
   </Obj>
</Objs>';

–The query for your first element with <TN>

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT obj.value('@RefId','int') AS ObjectId
      ,obj.value('(TN/@RefId)[1]','int') AS TNId
      ,t.value('text()[1]','nvarchar(max)') AS T_Content
FROM @xml.nodes('/Objs/Obj[TN]') A(obj) --<-- see the predicate for `[TN]`
CROSS APPLY A.obj.nodes('TN/T') B(t);

–Alternative for the previous if you can be sure, that there is just one single <TN> element:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT @xml.value('(/Objs/Obj[TN]/@RefId)[1]','int') AS ObjectId
      ,@xml.value('(/Objs/Obj[TN]/TN/@RefId)[1]','int') AS TNId
      ,t.value('text()[1]','nvarchar(max)') AS T_Content
FROM @xml.nodes('/Objs/Obj[TN]/TN/T') A(t);

–the query for the repeating <MS><S> elements

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
SELECT obj.value('@RefId','int') AS ObjectId
      --if appropriate you might use COALLESCE for the TN-ids
      ,obj.value('(TN/@RefId)[1]','int') AS TNId
      ,obj.value('(TNRef/@RefId)[1]','int') AS TNRefId
      ,obj.value('(MS/S[@N="ServerName"]/text())[1]','nvarchar(max)') AS ServerName
      ,obj.value('(MS/S[@N="DBName"]/text())[1]','nvarchar(max)') AS DBName
      ,obj.value('(MS/S[@N="UserName"]/text())[1]','nvarchar(max)') AS UserName
      ,obj.value('(MS/S[@N="RoleName"]/text())[1]','nvarchar(max)') AS RoleName
FROM @xml.nodes('/Objs/Obj') A(obj)

–and one more to retrieve each embedded structure separately. This allows for a step-wise approach (e.g. with an intermediate staging table).

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/powershell/2004/04')
,TheObjects AS
(
    SELECT obj.value('@RefId','int') AS ObjectId
          ,obj.query('TN') AS TN_Element 
          ,obj.query('TNRef') AS TNRef_Element 
          ,obj.query('MS') AS MS_Element 
    FROM @xml.nodes('/Objs/Obj') A(obj)
)
SELECT * 
FROM TheObjects;

The idea in short:

• We need to declare the existing default namespace
• we use .nodes() to dive into repeating elements
• We use .value() to retrieve data (either from @xml directly or from a Xml fragment coming from .nodes()).
• We use .query() to retrieve Xml fragments.