0

I've got XML in a column in an SQL table, and I get null back with every query I run. I've tried copying the XML into notepad and saving it as an XML file, then accessing it via PowerShell. Here's a copy of it:

<?xml version="1.0" encoding="utf-16"?>
< AppMgmtDigest xmlns="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

had to write it like this with the schema above because it didn't display as viewable code*

<Application AuthoringScopeId="ScopeId_932201A0-0C33-4C2C-9723-2975C4CCEBB5" LogicalName="Application_0e2a35f6-49a1-403f-adf5-d9f393571943" Version="4">
<DisplayInfo DefaultLanguage="en-US">
<Info Language="en-US">
<Title>Microsoft Office Excel Viewer</Title>
</Info>
</DisplayInfo>
<DeploymentTypes>
<DeploymentType AuthoringScopeId="ScopeId_932201A0-0C33-4C2C-9723-2975C4CCEBB5" LogicalName="DeploymentType_04fa8891-e832-486f-8d72-fc77c7dcffda" Version="3"/>
</DeploymentTypes>
<Title ResourceId="Res_1963789643">Microsoft Office Excel Viewer</Title>
<AutoInstall>true</AutoInstall>
<Owners>
<User Qualifier="LogonName" Id="abc123"/>
</Owners>
<Contacts>
<User Qualifier="LogonName" Id="abc123"/>
</Contacts>
</Application>
<DeploymentType AuthoringScopeId="ScopeId_932201A0-0C33-4C2C-9723-2975C4CCEBB5" LogicalName="DeploymentType_04fa8891-e832-486f-8d72-fc77c7dcffda" Version="3">
...
</DeploymentType>
</AppMgmtDigest>

I cannot query this document. in SQL, I'm trying something like this:

SomeName.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";(p1:AppMgmtDigest/p1:DeploymentType)[1]', 'nvarchar(max)') as Test

Now for some reason this works:

someVar.value('declare namespace p1="http://schemas.microsoft.com/SystemCenterConfigurationManager/2009/AppMgmtDigest";(p1:AppMgmtDigest/p1:Application/p1:AutoInstall)[1]', 'nvarchar(max)') = 'true'

Why does it need to be padded with those p1 declarations? Sorry, I'm new to XQuery.

Gilles Quénot
  • 173,512
  • 41
  • 224
  • 223
AnthonyC
  • 1
  • 1
  • Does this answer your question? [What does "xmlns" in XML mean?](https://stackoverflow.com/questions/1181888/what-does-xmlns-in-xml-mean) – Gilles Quénot May 17 '23 at 01:20
  • You need to learn what is a `namespace`. – Gilles Quénot May 17 '23 at 01:35
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky May 17 '23 at 02:05

0 Answers0